// Set ShowSaveFileDialog
public static ShowSaveFileDialogRe ShowSaveFileDialog(string tempFilename, FileInfo2 defaultFilename)
{
try
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel Files|*.xlsx";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.OverwritePrompt = true;
// 預設檔案名稱
if (defaultFilename != null)
{
saveFileDialog1.FileName = defaultFilename.getGetFileNameWithoutExtension();
saveFileDialog1.InitialDirectory = defaultFilename.getDirectoryName();
saveFileDialog1.RestoreDirectory = true;
}
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.OK && saveFileDialog1.FileName != "")
{
File.Copy(tempFilename, saveFileDialog1.FileName, true);
// 回傳資訊
return(new ShowSaveFileDialogRe
{
dialogResult = dr,
msg = saveFileDialog1.FileName
});
}
// 回傳資訊
return(new ShowSaveFileDialogRe
{
dialogResult = dr,
msg = saveFileDialog1.FileName
});
}
catch (IOException ex)
{
// 回傳資訊
return(new ShowSaveFileDialogRe
{
dialogResult = DialogResult.Abort,
msg = ex.Message
});
}
}
// 拖曳放入檔案event
void Form1_DragDrop(object sender, DragEventArgs e)
{
string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
foreach (string file in files)
{
FileInfo2 f = new FileInfo2(file);
// 取第1個檔案
if (f.isExcel())
{
bwGenExcelArgs.sourceFile = f.getFullName();
fromFilePath.Text = bwGenExcelArgs.sourceFile;
initFormWhenOpenExcel();
setComboboxItem(bwGenExcelArgs.sourceFile);
break;
}
}
}
private void Main2_Load(object sender, EventArgs e)
{
string version = System.Windows.Forms.Application.ProductVersion;
this.Text = String.Format("Excel add image by command line (version {0})", version);
outputExcelFileName.Text = "";
openExcel.Visible = false;
if (!(args.Length == 8))
{
string msg = @"
EXAMPLE:
ExcelAddImage.exe ""D:\te\source_excel"" ""D:\te\output_excel"" Y B C Y 1 80
parameter 1 : Source excel file (excluded .xlsx)
parameter 2 : Destination excel file (excluded .xlsx)
parameter 3 : Y or N, Show the SaveAs Dialog Box
parameter 4 : Image path column from source excel file
parameter 5 : Add image column from source excel file
parameter 6 : Y or N, Insert a blank column to add image
parameter 7 : 1 or 2 (PCX or JPG), Get image priority
parameter 8 : Image range of height from 30 to 100
";
Fun.showMessageBox(msg, "Error : Incorrect number of parameter");
Application.Exit();
}
// 檢查圖片高度參數範圍,超過範圍跳出訊息並關閉程式
if (Int32.Parse(args[7]) != Fun.imageHeightRange(Int32.Parse(args[7])))
{
Fun.showMessageBox(
String.Format("Check image range of height from 30 to 100"), "Error");
Application.Exit();
}
sourceFile = new FileInfo2(string.Format("{0}.xlsx", args[0]));
saveAsFile = new FileInfo2(string.Format("{0}.xlsx", args[1]));
if (!sourceFile.isFileExists())
{
Fun.showMessageBox(
String.Format("\"{0}\" source excel does not exist.",
sourceFile.getFullName()), "SaveAs error");
Application.Exit();
}
// 程式執行路徑
string executingDirectory = Fun.getExecutingDirectory();
// 讀取ini
var parser = new FileIniDataParser();
IniData data = parser.ReadFile(String.Format(@"{0}\{1}", executingDirectory, "Config.ini"));
imageDirectory = data["ExcelAddImage"]["ImageDirectory"];
imagePriority = data["ExcelAddImage"]["ImagePriority"];
// 檢查圖片路徑存在否
if (!Directory.Exists(imageDirectory))
{
Fun.showMessageBox(String.Format("\"{0}\" does not exist.", imageDirectory), "Error");
}
// 顯示SaveAs Dialog Box
if (args[2].ToUpper().Equals("Y"))
{
showSaveAsDialogBox = true;
}
else
{
showSaveAsDialogBox = false;
}
// Excel新增圖片的column是否要insert一欄
if (args[5].ToUpper().Equals("Y"))
{
excelColumnInsert = true;
}
else
{
excelColumnInsert = false;
}
// BwGenExcel參數Object
bwGenExcelArgs = new BwGenExcelArgs
{
sourceFile = sourceFile.getFullName(),
outputFile = saveAsFile.getFullName(),
excelImagePathColumnIdx = Fun.GetNumberFromExcelColumn(args[3]),
imageHeight = Int32.Parse(args[7]),
imagePriorityList = Fun.getExtPriorityList(Int32.Parse(args[6])),
imageDirectory = imageDirectory,
excelAddImageColumnIdx = Fun.GetNumberFromExcelColumn(args[4]),
excelColumnInsert = excelColumnInsert
};
// 線程產生Excel
BwGenExcel bw_DoWork = new BwGenExcel(bwGenExcelArgs);
bw = new BackgroundWorker();
bw.WorkerReportsProgress = true;
bw.WorkerSupportsCancellation = true;
bw.DoWork += new DoWorkEventHandler(bw_DoWork.DoWork);
bw.ProgressChanged += new ProgressChangedEventHandler(bw_ProgressChanged);
bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);
bw.RunWorkerAsync();
}
// 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();
}
}