public void ExportToXls(DataTable resultTable, Scan scan, bool showSource = false) { var reportTemplate = scan.Find(x => x.ResName == "XLS_REP" && x.Attr == attrName.System); if (reportTemplate != null) { //dataToXls(oSheet, scan, resultTable, xlsColor, startCol, startRow); ReportBuilder repot = new ReportBuilder(reportTemplate.XlsName, resultTable); } else { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; //object misvalue = System.Reflection.Missing.Value; oXL = new Excel.Application(); //-- new code string fileName = @"c:\Users\IKotvytskyi\Documents\my-template.xltx"; if (!File.Exists(fileName)) { fileName = ""; } oWB = oXL.Workbooks.Add(fileName); // <-- end code // oWB = oXL.Workbooks.Add(""); oSheet = oWB.ActiveSheet; drawTable(oSheet, scan, resultTable, Excel.XlRgbColor.rgbLightGreen); if (showSource) { drawTable(oSheet, scan, XlsTable, Excel.XlRgbColor.rgbAqua, resultTable.Columns.Count + 2, 1); } oXL.Visible = true; } }
void insertTable(Excel._Worksheet oSheet, Scan scan, DataTable resultTable, Excel.XlRgbColor xlsColor, int startCol = 1, int startRow = 1) { Excel.Range oRng; string[] title = resultTable.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); for (int i = 0; i < title.Count(); i++) { string colName = title[i]; var newName = scan.Find(x => x.IsActive && x.ResName == colName); if (newName != null) { if (newName.xlsColName != "") { title[i] = newName.xlsColName; } } } oRng = oSheet.get_Range(xlsAdress(startCol, startRow), xlsAdress(startCol - 1 + title.Length, startRow)); oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; oRng.Font.Bold = true; oRng.Value2 = title; oRng.Interior.Color = xlsColor; string[,] data = new string[resultTable.Rows.Count, resultTable.Columns.Count]; CultureInfo curCI = new CultureInfo(CultureInfo.CurrentCulture.Name); curCI.NumberFormat.NumberDecimalSeparator = Properties.Settings.Default.NumberDecimalSeparator; System.Threading.Thread.CurrentThread.CurrentCulture = curCI; for (int r = 0; r < resultTable.Rows.Count; r++) { for (int c = 0; c < resultTable.Columns.Count; c++) { if (resultTable.Rows[r][c].GetType().Name == "DateTime") { data[r, c] = ((DateTime)resultTable.Rows[r][c]).ToString(Properties.Settings.Default.ShortDatePattern); } else { data[r, c] = resultTable.Rows[r][c].ToString(); } } } oRng = oSheet.get_Range(xlsAdress(startCol, startRow + 1), xlsAdress(startCol - 1 + data.GetLength(1), startRow - 1 + data.GetLength(0) + 1)); oRng.Cells.Value = data; for (int i = 0; i < title.Count(); i++) { string colName = title[i]; var newName = scan.Find(x => x.IsActive && x.ResName == colName); if (newName != null) { if (newName.xlsFormat != "") { oRng = oSheet.get_Range(xlsAdress(startCol + i, startRow + 1), xlsAdress(startCol + i, startRow - 1 + data.GetLength(0) + 1)); oRng.EntireColumn.TextToColumns(); oRng.EntireColumn.NumberFormat = newName.xlsFormat; } } } oRng.EntireColumn.AutoFit(); System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo(CultureInfo.CurrentCulture.Name); }