// BackgroundWorker run
public void DoWork(object sender, DoWorkEventArgs e)
{
var bw = sender as BackgroundWorker;
int imagePathColumnIdx;
int addImageColumnIdx;
try
{
// 產生暫存Excel
string outExcelFileName = @"ExcelAddImageTemp.xlsx";
string outExcelFilePath = String.Format("{0}{1}", workTempDirectory, outExcelFileName);
string addExcelImage;
// EPPLUS只可以用在 *.xlsx
FileInfo newFile = new FileInfo(this.bwGenExcelArgs.sourceFile);
ExcelPackage pck = new ExcelPackage(newFile);
var worksheet = pck.Workbook.Worksheets[1];
imagePathColumnIdx = this.bwGenExcelArgs.excelImagePathColumnIdx;
addImageColumnIdx = this.bwGenExcelArgs.excelAddImageColumnIdx;
// 2018.8.6 增加可插入在欄位的任何位置
// 勾選放置圖片的欄位前插入1欄位
if (this.bwGenExcelArgs.excelColumnInsert)
{
worksheet.InsertColumn(addImageColumnIdx, 1);
// 放置圖片的欄位 <= path column --> path column + 1
// 放置圖片的欄位 > path column --> path column 不變
if (addImageColumnIdx <= imagePathColumnIdx)
{
imagePathColumnIdx++;
}
}
//放圖片的欄寬
worksheet.Column(addImageColumnIdx).Width = 30;
// 資料筆數
var rowCount = worksheet.Dimension.End.Row;
// 進度
bw.ReportProgress(0,
new BwGenExcelReport
{
reportType = 1,
begin = true,
maximum = rowCount,
value = 0,
msg = "Working ..."
}
);
String msgText;
for (int i = 1; i <= rowCount; i++)
{
// 抓取鞋型名稱
String shoeid;
if (worksheet.Cells[i, imagePathColumnIdx].Value != null)
{
shoeid = worksheet.Cells[i, imagePathColumnIdx].Value.ToString();
}
else
{
shoeid = "null";
}
msgText = String.Format("Row: {0}, Image: {1}", i, shoeid);
// 進度
bw.ReportProgress(0,
new BwGenExcelReport
{
reportType = 1,
begin = false,
maximum = 0,
value = i,
msg = msgText
}
);
// 設定列高 points = pixels * 72 / 96 (0.75) 加上0.03增加高度
worksheet.Row(i).Height = this.bwGenExcelArgs.imageHeight * 0.78;
// 圖片名稱路徑
//String picFullPath = analyzeImagePath(shoeid);
FileInfo2 picFullPath = new FileInfo2(analyzeImagePath(shoeid));
// 檢查有無圖檔
if (File.Exists(picFullPath.getFullName()))
{
addExcelImage = rotationTempFile(i);
try
{
// 如果是jpg檔不需要轉檔直接copy到暫存目錄
if (picFullPath.isJPG())
{
// 2018.8.1 nancy反應winxp出現"insufficient image data in file "G:\PROD\XXXX.jpg"錯誤
// 原因為jpg檔轉jpg檔
File.Copy(picFullPath.getFullName(), addExcelImage, true);
}
else
{
// Read first frame of pcx image
using (MagickImage image = new MagickImage(picFullPath.getFullName()))
{
// Save frame as jpg
image.Write(addExcelImage);
}
}
// 抓取圖片長寬
Image img = Image.FromFile(addExcelImage);
// 插入圖片
var picture = worksheet.Drawings.AddPicture(Generate15UniqueDigits(), img);
picture.SetPosition(i - 1, 2, addImageColumnIdx - 1, 2);
// 縮放比例
double h = img.Height;
double p = (this.bwGenExcelArgs.imageHeight / h) * 100;
picture.SetSize(Convert.ToInt32(p));
img.Dispose();
img = null;
}
catch (Exception ex)
{
// 回報錯誤
bw.ReportProgress(0,
new BwGenExcelReport
{
reportType = 2,
begin = false,
maximum = 0,
value = i,
msg = string.Format("{0}: {1}", "Error 1", ex.Message)
}
);
}
}
else
{
if (worksheet.Cells[i, addImageColumnIdx].Value == null)
{
worksheet.Cells[i, addImageColumnIdx].Value = "Image not found";
}
}
}
pck.SaveAs(new FileInfo(outExcelFilePath));
// Control UI (Status label)
msgText = String.Format("Finished..");
// 進度
bw.ReportProgress(0,
new BwGenExcelReport
{
reportType = 1,
begin = false,
maximum = 0,
value = rowCount,
msg = msgText
}
);
pck.Dispose();
e.Result = outExcelFilePath;
}
catch (Exception ex)
{
// 回報錯誤
bw.ReportProgress(0,
new BwGenExcelReport
{
reportType = 2,
begin = false,
maximum = 0,
value = 0,
msg = String.Format("{0}: {1}", "Error 2", ex.Message)
}
);
}
finally
{
GC.Collect();
}
}