개정판 aa73d664
issue #000: gembox 추가
Change-Id: Id37f615a3b5f0d9ac8f07b1c48effa1c718de2cf
| KCOM/Controls/CheckList.xaml.cs | ||
|---|---|---|
| 21 | 21 |
using KCOM.Controls; |
| 22 | 22 |
using KCOM.Common; |
| 23 | 23 |
using IKCOM; |
| 24 |
using Excel = Microsoft.Office.Interop.Excel; |
|
| 25 | 24 |
using System.Text.RegularExpressions; |
| 25 |
using GemBox.Spreadsheet; |
|
| 26 |
using System.IO; |
|
| 26 | 27 |
|
| 27 | 28 |
namespace KCOM.Controls |
| 28 | 29 |
{
|
| ... | ... | |
| 384 | 385 |
|
| 385 | 386 |
#region Excel Export |
| 386 | 387 |
|
| 388 |
[Obsolete] |
|
| 387 | 389 |
private void ExportEvent(object sender, RoutedEventArgs e) |
| 388 | 390 |
{
|
| 389 | 391 |
try |
| ... | ... | |
| 418 | 420 |
using (var client = new System.Net.WebClient()) |
| 419 | 421 |
{
|
| 420 | 422 |
client.DownloadFile(url, dialog.FileName); |
| 421 |
|
|
| 422 |
Excel.Application application = null; |
|
| 423 |
Excel.Workbook workbook = null; |
|
| 424 |
Excel.Worksheet worksheet = null; |
|
| 425 |
|
|
| 423 |
SpreadsheetInfo.SetLicense(Properties.Settings.Default.GemBoxLicense);// "EXK0-W4HZ-N518-IMEW"); |
|
| 424 |
ExcelFile workbook = null; |
|
| 425 |
ExcelWorksheet worksheet = null; |
|
| 426 | 426 |
try |
| 427 | 427 |
{
|
| 428 |
// Excel 첫번째 워크시트 가져오기 |
|
| 429 |
application = new Excel.Application(); |
|
| 430 |
workbook = application.Workbooks.Open(dialog.FileName); |
|
| 431 |
worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item("Sheet1");
|
|
| 428 |
// Excel 첫번째 워크시트 가져오기 |
|
| 429 |
workbook = ExcelFile.Load(dialog.FileName); |
|
| 430 |
worksheet = workbook.Worksheets[0]; |
|
| 432 | 431 |
|
| 433 | 432 |
// 데이타 넣기 |
| 434 |
int h = 9;
|
|
| 435 |
int w = 2;
|
|
| 433 |
int h = 8;
|
|
| 434 |
int w = 1;
|
|
| 436 | 435 |
string project_no = App.ViewInfo.ProjectNO; |
| 437 |
worksheet.Cells[2, w + 8] = project_no; |
|
| 438 |
//Logger.sendReqLog("GetProjectName: ", project_no, 1);
|
|
| 436 |
worksheet.Cells[1, w + 8].Value = project_no; |
|
| 439 | 437 |
|
| 440 | 438 |
string project_name = Common.ViewerDataModel.Instance.SystemMain.dzMainMenu.BaseClient.GetProjectName(project_no); |
| 441 | 439 |
if (project_name != null || project_name != "") |
| ... | ... | |
| 446 | 444 |
{
|
| 447 | 445 |
//Logger.sendResLog("GetProjectName", "FALSE", 1);
|
| 448 | 446 |
} |
| 449 |
worksheet.Cells[3, w + 8] = project_name; |
|
| 447 |
worksheet.Cells[2, w + 8].Value = project_name; |
|
| 448 |
worksheet.Cells[3, w + 8].Value = (mainWindow as MainWindow).dzMainMenu._DocItem.DOCUMENT_NO; |
|
| 449 |
worksheet.Cells[4, w + 8].Value = (mainWindow as MainWindow).dzMainMenu.userData.NAME + " (" + App.ViewInfo.UserID + ")";
|
|
| 450 |
worksheet.Cells[5, w + 8].Value = DateTime.Now.ToString("yyyy.MM.dd");
|
|
| 450 | 451 |
|
| 451 |
worksheet.Cells[4, w + 8] = (mainWindow as MainWindow).dzMainMenu._DocItem.DOCUMENT_NO; |
|
| 452 |
worksheet.Cells[5, w + 8] = (mainWindow as MainWindow).dzMainMenu.userData.NAME + " (" + App.ViewInfo.UserID + ")";
|
|
| 453 |
worksheet.Cells[6, w + 8] = DateTime.Now.ToString("yyyy.MM.dd");
|
|
| 454 |
|
|
| 455 |
Excel.Range Range_I = worksheet.Range["K9"]; |
|
| 456 |
Excel.Range Range_O = null; |
|
| 457 |
Excel.Borders border_ = null; |
|
| 452 |
ExcelCell Range_I = worksheet.Cells["K9"]; |
|
| 458 | 453 |
|
| 459 | 454 |
var Rev = Lists.Select(info => info.REVISION).Distinct(); |
| 460 | 455 |
string[] Cell = { "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };
|
| 461 | 456 |
string[] Range = { "L9", "M9", "N9", "O9", "P9", "Q9", "R9", "S9", "T9", "U9", "V9", "W9", "X9", "Y9", "Z9", "AA9", "AB9", "AC9", "AD9" };
|
| 462 | 457 |
string Ran = "K"; |
| 463 | 458 |
|
| 464 |
for (int i = 0; i < rev_cnt - 1; i++) |
|
| 465 |
{
|
|
| 466 |
worksheet.Cells[9, 12 + i] = Cell[i]; |
|
| 467 |
Range_O = worksheet.Range[Range[i]]; |
|
| 468 |
border_ = Range_O.Borders; |
|
| 469 |
border_.LineStyle = Range_I.Borders.LineStyle; |
|
| 470 |
border_.Weight = Range_I.Borders.Weight; |
|
| 471 |
Range_O.Interior.Color = Range_I.Interior.Color; |
|
| 472 |
} |
|
| 473 |
|
|
| 474 |
worksheet.Range[worksheet.Cells[8, 11], worksheet.Cells[8, 11 + rev_cnt - 1]].Merge(); |
|
| 459 |
worksheet.Cells.GetSubrangeAbsolute(8, 11, 8, 11 + rev_cnt - 1).Merged = true; |
|
| 475 | 460 |
|
| 476 | 461 |
if (rev_cnt != 1) |
| 477 | 462 |
{
|
| 478 | 463 |
Ran = Range[rev_cnt - 2].Substring(0, Range[rev_cnt - 2].Length - 1); |
| 479 | 464 |
} |
| 480 |
Excel.Range iRange = worksheet.Range["B10:" + Ran + "10"];
|
|
| 481 |
|
|
| 465 |
CellRange iRange = worksheet.Cells.GetSubrange("B10:" + Ran + "10");
|
|
| 466 |
|
|
| 482 | 467 |
//필터 기능 추가 |
| 483 |
Excel.Range Filter_Range = worksheet.Range["B9:" + Ran + "9"];
|
|
| 484 |
Filter_Range.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
|
|
| 485 |
|
|
| 468 |
CellRange Filter_Range = worksheet.Cells.GetSubrange("B9:" + Ran + "9");
|
|
| 469 |
Filter_Range.Filter(true);
|
|
| 470 |
ExcelCell Range_b = worksheet.Cells["B10"]; |
|
| 486 | 471 |
Lists.ForEach(data => |
| 487 | 472 |
{
|
| 488 | 473 |
h++; |
| 489 |
|
|
| 490 |
Excel.Range oRange = worksheet.Range["B" + h + ":" + Ran + h];
|
|
| 491 |
oRange.RowHeight = iRange.RowHeight;
|
|
| 492 |
Excel.Borders border = oRange.Borders;
|
|
| 493 |
border.LineStyle = Excel.XlLineStyle.xlContinuous;
|
|
| 494 |
border.Weight = 2d;
|
|
| 474 |
// Get the range. |
|
| 475 |
CellRange oRange = worksheet.Cells.GetSubrange("B" + (h + 1) + ":" + Ran + (h + 1));
|
|
| 476 |
foreach (var cell in oRange)
|
|
| 477 |
{
|
|
| 478 |
cell.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);
|
|
| 479 |
}
|
|
| 495 | 480 |
|
| 496 | 481 |
if (data.IMAGE_PATH != null) |
| 497 | 482 |
{
|
| 498 |
Excel.Range ImageRange = worksheet.Cells[h, w + 2]; |
|
| 499 |
|
|
| 483 |
ExcelCell ImageRange = worksheet.Cells[h, w + 2]; |
|
| 484 |
ImageRange.Row.Height = Range_b.Row.Height; |
|
| 485 |
//ImageRange.Style.Font.Size = 10; |
|
| 500 | 486 |
System.Drawing.Image I_Image = byteArrayToImage(data.IMAGE_PATH); |
| 501 | 487 |
System.Drawing.Image o_Image = Resize(I_Image, 80, 80); |
| 502 | 488 |
System.Windows.Forms.Clipboard.SetDataObject(o_Image, true); |
| 503 |
|
|
| 504 |
ImageRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; |
|
| 505 |
ImageRange.Cells.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; |
|
| 506 |
|
|
| 507 |
worksheet.Paste(ImageRange.Cells, "TEST"); |
|
| 508 |
|
|
| 509 |
foreach (Microsoft.Office.Interop.Excel.Shape shp in worksheet.Shapes) |
|
| 510 |
{
|
|
| 511 |
if (shp.TopLeftCell.Address != ImageRange.Address) |
|
| 512 |
continue; |
|
| 513 |
|
|
| 514 |
double Left = shp.TopLeftCell.Left + (shp.TopLeftCell.Width - shp.Width) / 2; |
|
| 515 |
double Top = shp.TopLeftCell.Top + (shp.TopLeftCell.Height - shp.Height) / 2; |
|
| 516 |
|
|
| 517 |
shp.Left = float.Parse(Left.ToString()); |
|
| 518 |
shp.Top = float.Parse(Top.ToString()); |
|
| 519 |
|
|
| 520 |
break; |
|
| 521 |
} |
|
| 489 |
ImageRange.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; |
|
| 490 |
ImageRange.Style.VerticalAlignment = VerticalAlignmentStyle.Top; |
|
| 491 |
byte[] imageData = data.IMAGE_PATH; |
|
| 492 |
MemoryStream memoryStream = new MemoryStream(); |
|
| 493 |
o_Image.Save(memoryStream, System.Drawing.Imaging.ImageFormat.Jpeg); |
|
| 494 |
int column = 2; // 이미지를 삽입할 열 |
|
| 495 |
int row = h; // 이미지를 삽입할 행 |
|
| 496 |
// 이미지를 워크시트에 추가 |
|
| 497 |
ExcelPicture picture = worksheet.Pictures.Add(memoryStream, PositioningMode.FreeFloating, new AnchorCell(ImageRange.Column, ImageRange.Row, 0, 0), ExcelPictureFormat.Jpeg); |
|
| 498 |
|
|
| 499 |
//foreach (Microsoft.Office.Interop.Excel.Shape shp in worksheet.Shapes) |
|
| 500 |
//{
|
|
| 501 |
// if (shp.TopLeftCell.Address != ImageRange.Address) |
|
| 502 |
// continue; |
|
| 503 |
|
|
| 504 |
// double Left = shp.TopLeftCell.Left + (shp.TopLeftCell.Width - shp.Width) / 2; |
|
| 505 |
// double Top = shp.TopLeftCell.Top + (shp.TopLeftCell.Height - shp.Height) / 2; |
|
| 506 |
|
|
| 507 |
// shp.Left = float.Parse(Left.ToString()); |
|
| 508 |
// shp.Top = float.Parse(Top.ToString()); |
|
| 509 |
|
|
| 510 |
// break; |
|
| 511 |
//} |
|
| 522 | 512 |
} |
| 523 | 513 |
|
| 524 | 514 |
#region Excel Bind |
| 525 |
worksheet.Cells[h, w] = data.REVISION; |
|
| 526 |
worksheet.Cells[h, w + 1] = data.PAGENUMBER; |
|
| 527 |
worksheet.Cells[h, w + 3] = data.TODOLIST; |
|
| 528 |
worksheet.Cells[h, w + 4] = data.STATUS.ToUpper() == "TRUE" ? "closed" : "open"; |
|
| 529 |
worksheet.Cells[h, w + 5] = data.STATUS_DESC; |
|
| 530 |
worksheet.Cells[h, w + 6] = data.VENDOR; |
|
| 531 |
worksheet.Cells[h, w + 7] = data.REMARK; |
|
| 532 |
worksheet.Cells[h, w + 8] = data.REPLY; |
|
| 533 |
worksheet.Cells[h, w + 9] = data.REV_0 == null ? "" : data.REV_0.Replace(" ", " / ");
|
|
| 534 |
worksheet.Cells[h, w + 10] = data.REV_1 == null ? "" : data.REV_1.Replace(" ", " / ");
|
|
| 535 |
worksheet.Cells[h, w + 11] = data.REV_2 == null ? "" : data.REV_2.Replace(" ", " / ");
|
|
| 536 |
worksheet.Cells[h, w + 12] = data.REV_3 == null ? "" : data.REV_3.Replace(" ", " / ");
|
|
| 537 |
worksheet.Cells[h, w + 13] = data.REV_4 == null ? "" : data.REV_4.Replace(" ", " / ");
|
|
| 538 |
worksheet.Cells[h, w + 14] = data.REV_5 == null ? "" : data.REV_5.Replace(" ", " / ");
|
|
| 539 |
worksheet.Cells[h, w + 15] = data.REV_6 == null ? "" : data.REV_6.Replace(" ", " / ");
|
|
| 540 |
worksheet.Cells[h, w + 16] = data.REV_7 == null ? "" : data.REV_7.Replace(" ", " / ");
|
|
| 541 |
worksheet.Cells[h, w + 17] = data.REV_8 == null ? "" : data.REV_8.Replace(" ", " / ");
|
|
| 542 |
worksheet.Cells[h, w + 18] = data.REV_9 == null ? "" : data.REV_9.Replace(" ", " / ");
|
|
| 543 |
worksheet.Cells[h, w + 19] = data.REV_10 == null ? "" : data.REV_10.Replace(" ", " / ");
|
|
| 544 |
worksheet.Cells[h, w + 20] = data.REV_11 == null ? "" : data.REV_11.Replace(" ", " / ");
|
|
| 545 |
worksheet.Cells[h, w + 21] = data.REV_12 == null ? "" : data.REV_12.Replace(" ", " / ");
|
|
| 546 |
worksheet.Cells[h, w + 22] = data.REV_13 == null ? "" : data.REV_13.Replace(" ", " / ");
|
|
| 547 |
worksheet.Cells[h, w + 23] = data.REV_14 == null ? "" : data.REV_14.Replace(" ", " / ");
|
|
| 548 |
worksheet.Cells[h, w + 24] = data.REV_15 == null ? "" : data.REV_15.Replace(" ", " / ");
|
|
| 549 |
worksheet.Cells[h, w + 25] = data.REV_16 == null ? "" : data.REV_16.Replace(" ", " / ");
|
|
| 550 |
worksheet.Cells[h, w + 26] = data.REV_17 == null ? "" : data.REV_17.Replace(" ", " / ");
|
|
| 551 |
worksheet.Cells[h, w + 27] = data.REV_18 == null ? "" : data.REV_18.Replace(" ", " / ");
|
|
| 552 |
worksheet.Cells[h, w + 28] = data.REV_19 == null ? "" : data.REV_19.Replace(" ", " / ");
|
|
| 515 |
worksheet.Cells[h, w].Value = data.REVISION;
|
|
| 516 |
worksheet.Cells[h, w + 1].Value = data.PAGENUMBER;
|
|
| 517 |
worksheet.Cells[h, w + 3].Value = data.TODOLIST;
|
|
| 518 |
worksheet.Cells[h, w + 4].Value = data.STATUS.ToUpper() == "TRUE" ? "closed" : "open";
|
|
| 519 |
worksheet.Cells[h, w + 5].Value = data.STATUS_DESC;
|
|
| 520 |
worksheet.Cells[h, w + 6].Value = data.VENDOR;
|
|
| 521 |
worksheet.Cells[h, w + 7].Value = data.REMARK;
|
|
| 522 |
worksheet.Cells[h, w + 8].Value = data.REPLY;
|
|
| 523 |
worksheet.Cells[h, w + 9].Value = data.REV_0 == null ? "" : data.REV_0.Replace(" ", " / ");
|
|
| 524 |
worksheet.Cells[h, w + 10].Value = data.REV_1 == null ? "" : data.REV_1.Replace(" ", " / ");
|
|
| 525 |
worksheet.Cells[h, w + 11].Value = data.REV_2 == null ? "" : data.REV_2.Replace(" ", " / ");
|
|
| 526 |
worksheet.Cells[h, w + 12].Value = data.REV_3 == null ? "" : data.REV_3.Replace(" ", " / ");
|
|
| 527 |
worksheet.Cells[h, w + 13].Value = data.REV_4 == null ? "" : data.REV_4.Replace(" ", " / ");
|
|
| 528 |
worksheet.Cells[h, w + 14].Value = data.REV_5 == null ? "" : data.REV_5.Replace(" ", " / ");
|
|
| 529 |
worksheet.Cells[h, w + 15].Value = data.REV_6 == null ? "" : data.REV_6.Replace(" ", " / ");
|
|
| 530 |
worksheet.Cells[h, w + 16].Value = data.REV_7 == null ? "" : data.REV_7.Replace(" ", " / ");
|
|
| 531 |
worksheet.Cells[h, w + 17].Value = data.REV_8 == null ? "" : data.REV_8.Replace(" ", " / ");
|
|
| 532 |
worksheet.Cells[h, w + 18].Value = data.REV_9 == null ? "" : data.REV_9.Replace(" ", " / ");
|
|
| 533 |
worksheet.Cells[h, w + 19].Value = data.REV_10 == null ? "" : data.REV_10.Replace(" ", " / ");
|
|
| 534 |
worksheet.Cells[h, w + 20].Value = data.REV_11 == null ? "" : data.REV_11.Replace(" ", " / ");
|
|
| 535 |
worksheet.Cells[h, w + 21].Value = data.REV_12 == null ? "" : data.REV_12.Replace(" ", " / ");
|
|
| 536 |
worksheet.Cells[h, w + 22].Value = data.REV_13 == null ? "" : data.REV_13.Replace(" ", " / ");
|
|
| 537 |
worksheet.Cells[h, w + 23].Value = data.REV_14 == null ? "" : data.REV_14.Replace(" ", " / ");
|
|
| 538 |
worksheet.Cells[h, w + 24].Value = data.REV_15 == null ? "" : data.REV_15.Replace(" ", " / ");
|
|
| 539 |
worksheet.Cells[h, w + 25].Value = data.REV_16 == null ? "" : data.REV_16.Replace(" ", " / ");
|
|
| 540 |
worksheet.Cells[h, w + 26].Value = data.REV_17 == null ? "" : data.REV_17.Replace(" ", " / ");
|
|
| 541 |
worksheet.Cells[h, w + 27].Value = data.REV_18 == null ? "" : data.REV_18.Replace(" ", " / ");
|
|
| 542 |
worksheet.Cells[h, w + 28].Value = data.REV_19 == null ? "" : data.REV_19.Replace(" ", " / ");
|
|
| 553 | 543 |
#endregion |
| 554 | 544 |
}); |
| 545 |
|
|
| 546 |
for (int columnIndex = 1; columnIndex < 11; ++columnIndex) |
|
| 547 |
{
|
|
| 548 |
worksheet.Columns[columnIndex].AutoFit(); |
|
| 549 |
} |
|
| 555 | 550 |
|
| 556 |
worksheet.Columns.AutoFit();
|
|
| 551 |
worksheet.Columns["G"].Width = Convert.ToInt32(worksheet.Columns["G"].Width * 1.1);
|
|
| 557 | 552 |
// 엑셀파일 저장 |
| 558 |
workbook.Save(); |
|
| 559 |
workbook.Close(true); |
|
| 560 |
application.Quit(); |
|
| 553 |
workbook.Save(dialog.FileName); |
|
| 554 |
|
|
| 561 | 555 |
} |
| 562 | 556 |
finally |
| 563 |
{
|
|
| 564 |
System.Diagnostics.Process[] ExCel = System.Diagnostics.Process.GetProcessesByName("EXCEL");
|
|
| 565 |
if (ExCel.Count() != 0) |
|
| 566 |
{
|
|
| 567 |
ExCel[0].Kill(); |
|
| 568 |
} |
|
| 569 |
|
|
| 557 |
{
|
|
| 570 | 558 |
// Clean up |
| 571 | 559 |
ReleaseExcelObject(worksheet); |
| 572 | 560 |
ReleaseExcelObject(workbook); |
| 573 |
ReleaseExcelObject(application); |
|
| 574 | 561 |
} |
| 575 | 562 |
} |
| 576 | 563 |
DialogMessage_Alert("Success");
|
| ... | ... | |
| 585 | 572 |
|
| 586 | 573 |
} |
| 587 | 574 |
|
| 575 |
private static void SetWidth(ExcelColumn column, double width) |
|
| 576 |
{
|
|
| 577 |
column.SetWidth( |
|
| 578 |
Math.Truncate((width * 7 + 5) / 7 * 256) / 256, |
|
| 579 |
LengthUnit.ZeroCharacterWidth); |
|
| 580 |
} |
|
| 581 |
|
|
| 582 |
|
|
| 588 | 583 |
public System.Drawing.Image byteArrayToImage(byte[] byteArrayIn) |
| 589 | 584 |
{
|
| 590 | 585 |
using (var ms = new System.IO.MemoryStream(byteArrayIn)) |
내보내기 Unified diff
