private void generateResultExcel(DataSet ds, string filePathAndName)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet = null;
// 建立excel 文件
xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
// 建立Sheets and name
xlWorkBook = xlApp.Workbooks.Add(Missing.Value);
int sheetCount = 1;
foreach (DataTable dt in ds.Tables)
{
xlWorkBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlWorkBook.Worksheets[sheetCount].Name = dt.TableName;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[dt.TableName];
xlWorkSheet.Cells.NumberFormatLocal = "@";
xlWorkSheet.Cells.Font.Size = 10;
// fill content to sheet name "Detail"
MvExcelReport.fastFillDataIntoExcel(dt, xlWorkSheet, "A1", true);
}
// save file to xlsx
xlApp.DisplayAlerts = false;
try
{
xlWorkBook.SaveAs(filePathAndName, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (COMException)
{
MessageBox.Show("Please make the file is not be opened. " + Environment.NewLine + filePathAndName);
}
finally
{
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
}
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
}
private void btnGetSummaryExcel_Click(object sender, EventArgs e)
{
string workingPath = @"D:\";
string searchTerm = "PC-0";
StringBuilder sb = new StringBuilder();
DataTable dt = new DataTable("SummaryReport");
dt.Columns.Add("MachineName");
dt.Columns.Add("ExtensionType");
dt.Columns.Add("IncludeExtension");
dt.Columns.Add("Amount");
foreach (var fi in Directory.EnumerateDirectories(workingPath).Where(m => m.StartsWith(workingPath + searchTerm)))
{
string machineName = fi.Replace(workingPath, "");
string reportFileName = string.Format("{0}.report.txt", Path.Combine(fi.ToString(), machineName));
if (File.Exists(reportFileName) == false)
{
continue;
}
string[] fileList = File.ReadAllLines(reportFileName);
foreach (string lineData in fileList)
{
DataRow dr = dt.NewRow();
string[] tempData = lineData.Split('\t');
dr["MachineName"] = tempData[0];
dr["ExtensionType"] = tempData[1];
dr["IncludeExtension"] = tempData[2];
dr["Amount"] = tempData[3];
dt.Rows.Add(dr);
}
}
// 產生Summary excel
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
// 建立excel 文件
xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
// 建立Sheets and name
xlWorkBook = xlApp.Workbooks.Add(Missing.Value);
xlWorkBook.Worksheets[1].Name = dt.TableName;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[dt.TableName];
xlWorkSheet.Cells.NumberFormatLocal = "@";
xlWorkSheet.Cells.Font.Size = 10;
// fill content to sheet name "Detail"
MvExcelReport.fastFillDataIntoExcel(dt, xlWorkSheet, "A1", true);
// save file to xlsx
xlApp.DisplayAlerts = false;
string destFilePathAndName = workingPath + "SummaryReport.xlsx";
try
{
xlWorkBook.SaveAs(destFilePathAndName, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (COMException)
{
MessageBox.Show("Please make the file is not be opened. " + Environment.NewLine + destFilePathAndName);
}
finally
{
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
}
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
}
public void buildLocalScannedExcel(string workingDirectory)
{
if (Directory.Exists(workingDirectory) == false)
{
return;
}
DataSet sheetsData = new DataSet("SheetsData");
// 1. 設定處理目錄
DataTable summaryDt = new DataTable("Summary");
summaryDt.Columns.Add("Extension");
summaryDt.Columns.Add("IncludeExtension");
summaryDt.Columns.Add("Amount");
// 2. 整理summary內容
// 找出符合的summary檔案
string searchTerm = Environment.MachineName + DefinedFileType.SummaryName + ".";
foreach (var fi in Directory.EnumerateFiles(workingDirectory).Where(m => m.StartsWith(workingDirectory + searchTerm)))
{
string tmpFileName = fi.ToString();
string extensionName = "";
string includeExtensionName = "";
DataRow summaryDr = summaryDt.NewRow();
// 取出Extension
tmpFileName = tmpFileName.Replace(workingDirectory + searchTerm, "");
tmpFileName = tmpFileName.Replace(".txt", "");
extensionName = tmpFileName.Substring(0, tmpFileName.IndexOf("_"));
// 取出IncludeExtension
includeExtensionName = tmpFileName.Substring(tmpFileName.LastIndexOf("_") + 1, tmpFileName.Length - tmpFileName.LastIndexOf("_") - 1);
// 判斷 extensionName 與 includeExtensionName 是否相同
// 相同 : 非壓縮檔
// 不同 : 壓縮檔, 加入Compress字串
if (extensionName.Equals(includeExtensionName) == false)
{
extensionName = "comress_" + extensionName;
}
summaryDr["Extension"] = extensionName;
summaryDr["IncludeExtension"] = includeExtensionName;
// 取得檔案內所有Line Data
string[] fileList = File.ReadAllLines(fi.ToString());
DataTable tempDt = new DataTable(extensionName + "_Include_" + includeExtensionName);
tempDt.Columns.Add("Amount");
tempDt.Columns.Add("FileName");
foreach (string lineDate in fileList)
{
DataRow dr = tempDt.NewRow();
string[] tmpString = lineDate.Split('\t');
dr["Amount"] = tmpString[0];
dr["FileName"] = tmpString[1];
tempDt.Rows.Add(dr);
}
sheetsData.Tables.Add(tempDt);
summaryDr["Amount"] = fileList.Length;
summaryDt.Rows.Add(summaryDr);
}
sheetsData.Tables.Add(summaryDt);
// 3. 輸出excel
string outputFilePathAndName = workingDirectory + Environment.MachineName + ".summary_local.xlsx";
if (File.Exists(outputFilePathAndName) == true)
{
// 如果無法刪除, 就變更檔名
try
{
File.Delete(outputFilePathAndName);
}
catch (IOException)
{
outputFilePathAndName = workingDirectory + "summary_local_" + new Random(DateTime.Now.Millisecond).Next().ToString() + ".xlsx";
}
}
MvExcelReport.writeExcelByDataSet(sheetsData, outputFilePathAndName);
// release parameter
sheetsData.Dispose();
summaryDt.Dispose();
sheetsData = null;
summaryDt = null;
searchTerm = null;
outputFilePathAndName = null;
}
private void btnOk_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
bool blnResult = false;
// get the report start's date and end date
startDate = dateTimePicker1.Value;
endDate = dateTimePicker2.Value;
if (startDate.CompareTo(endDate) > 0)
{
if (DateTime.Compare(startDate.Date, endDate.Date) != 0)
{
MessageBox.Show("結束日期需大於等於起始日期");
return;
}
}
string result = cboReportType.SelectedValue == null ? "" : cboReportType.SelectedValue.ToString();
if (result.Length == 0)
{
MessageBox.Show("Please choice the report type");
return;
}
// Find the report type
DefinedReport.ErpReportType reportList = DefinedReport.ErpReportType.NONE;
try
{
reportList = (DefinedReport.ErpReportType)Enum.Parse(typeof(DefinedReport.ErpReportType), result);
}
catch (ArgumentException)
{ }
MvExcelReport report = new MvExcelReport();
switch (reportList)
{
case DefinedReport.ErpReportType.MocP10Auto:
endDate = System.DateTime.Now;
startDate = DateTime.Parse(endDate.AddMonths(-4).ToShortDateString());
fileNameAndPath = string.Format("{0}{1}_{2}.xlsx", workingDirectory, DefinedReport.ErpReportType.MocP10Auto.ToString(), endDate.ToString("yyyyMMdd"));
Console.WriteLine(System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss.fff") + " start report");
ds = MvDbDao.collectData_MocP10Auto_VB6(System.DateTime.Now);
blnResult = report.generateMocP10Auto(fileNameAndPath, ds);
if (blnResult == false)
{
MessageBox.Show("generate MocP10Auto fail");
}
else
{
MessageBox.Show(string.Format("generate {0} done {1}{2}", DefinedReport.ErpReportType.MocP10Auto.ToString(), Environment.NewLine, fileNameAndPath));
}
break;
case DefinedReport.ErpReportType.BomP09:
result = cboBomType.SelectedValue == null ? "" : cboBomType.SelectedValue.ToString();
if (result.Length == 0)
{
MessageBox.Show("Please choice the BOM type");
return;
}
ds = MvDbDao.collectData_BomP09_VB6(new string[] { result });
fileNameAndPath = string.Format("{0}{1}_{2}.xlsx", workingDirectory, DefinedReport.ErpReportType.BomP09.ToString(), endDate.ToString("yyyyMMdd"));
blnResult = report.generateBomP09_NoPrice(fileNameAndPath, ds);
if (blnResult == false)
{
MessageBox.Show("generate MocP10Auto fail");
}
else
{
MessageBox.Show(string.Format("generate {0} done {1}{2}", DefinedReport.ErpReportType.BomP09.ToString(), Environment.NewLine, fileNameAndPath));
}
break;
case DefinedReport.ErpReportType.BomP09_Multi:
if (chkBomListBox.CheckedItems.Count <= 0)
{
MessageBox.Show("please checked more than one");
return;
}
List <string> selectedList = new List <string>();
foreach (var itemChecked in chkBomListBox.CheckedItems)
{
var row = (itemChecked as DataRowView).Row;
selectedList.Add(row["MC001"].ToString());
}
ds = MvDbDao.collectData_BomP09_VB6(selectedList.ToArray <string>());
fileNameAndPath = string.Format("{0}{1}_{2}.xlsx", workingDirectory, DefinedReport.ErpReportType.BomP09_Multi.ToString(), endDate.ToString("yyyyMMdd"));
blnResult = report.generateBomP09_NoPrice(fileNameAndPath, ds);
if (blnResult == false)
{
MessageBox.Show("generate MocP10Auto fail");
}
else
{
MessageBox.Show(string.Format("generate {0} done {1}{2}", DefinedReport.ErpReportType.BomP09_Multi.ToString(), Environment.NewLine, fileNameAndPath));
}
break;
default:
MessageBox.Show("Can't find the report type as " + result);
break;
}
}
