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;
}