private static Excel.Range PutSampleData(Excel.Worksheet workSheet) { workSheet.Cells[2, 2].Value = "Date"; workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[2, 3].Value = "Columns1"; workSheet.Cells[3, 3].Value = 25; workSheet.Cells[4, 3].Value = 33; workSheet.Cells[5, 3].Value = 30; workSheet.Cells[6, 3].Value = 22; workSheet.Cells[2, 4].Value = "Column2"; workSheet.Cells[3, 4].Value = 25; workSheet.Cells[4, 4].Value = 33; workSheet.Cells[5, 4].Value = 30; workSheet.Cells[6, 4].Value = 22; workSheet.Cells[2, 5].Value = "Column3"; workSheet.Cells[3, 5].Value = 25; workSheet.Cells[4, 5].Value = 33; workSheet.Cells[5, 5].Value = 30; workSheet.Cells[6, 5].Value = 22; return workSheet.Range("$B2:$E6"); }
public void AddDatasetIntoWorksheet(string Title,string workSheetName, DataSet ds, Excel.Worksheet workSheet, Excel.Workbook workbook) { // font action workSheet.Name = workSheetName; workSheet.Range("A2").Value = Title; workSheet.Range("A2").Font.Name = "Arial"; workSheet.Range("A2").Font.Size = 10; workSheet.Range("A2").Font.Bold = true; workSheet.Range("A2").Font.Italic = true; workSheet.Range("A2").Font.Underline = true; // setup rows and columns workSheet.Columns[1].AutoFit(); workSheet.Columns[2].ColumnWidth = 25; workSheet.Columns[3].ColumnWidth = 25; workSheet.Columns[4].ColumnWidth = 25; workSheet.Columns[5].ColumnWidth = 25; CultureInfo cultureInfo = NetOffice.Settings.ThreadCulture; List<string> colNamesToHighlight = GetColNamesToHighlight(); List<string> colNamesToHighlight_Red = GetColNamesToHighlight_Red(); int RowToStartHighlight = 0; int TotalsRow = 0; decimal TotalOfInactiveOver90 = 0; decimal TotalOfInactiveOver60 = 0; List<string> alpha = AToZ(); int rowNum = 5; int colcount = 0; //set certain columns to text format workSheet.Columns[3].NumberFormat = "@"; workSheet.Columns[14].NumberFormat = "@"; workSheet.Columns[17].NumberFormat = "@"; workSheet.Columns[18].NumberFormat = "@"; try { foreach (DataTable table in ds.Tables) { //write column headings foreach (DataRow row in table.Rows) { foreach (DataColumn column in table.Columns) { workSheet.Range(alpha[colcount] + rowNum.ToString()).Value = column.ColumnName; workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Bold = true; colcount += 1; } break; } colcount = 0; rowNum = 6; foreach (DataRow row in table.Rows) { foreach (DataColumn column in table.Columns) { workSheet.Range(alpha[colcount] + rowNum.ToString()).Value = row[column]; string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator); workSheet.Range(alpha[colcount] + rowNum.ToString()).NumberFormat = Pattern2; if (row[column].ToString().ToLower() == "inactive>60") RowToStartHighlight = rowNum; if (row[column].ToString().ToLower() == "totals") TotalsRow = rowNum; if (TotalsRow == rowNum) { workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Bold = true; } if (IsColToHighlight(colNamesToHighlight, column.ColumnName) && RowToStartHighlight == rowNum) { workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Color = ToDouble(System.Drawing.Color.Green); workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(198, 239, 206)); TotalOfInactiveOver90 += Convert.ToDecimal(workSheet.Range(alpha[colcount] + rowNum.ToString()).Value); } if (IsColToHighlight(colNamesToHighlight_Red, column.ColumnName) && RowToStartHighlight == rowNum) { workSheet.Range(alpha[colcount] + rowNum.ToString()).Font.Color = ToDouble(System.Drawing.Color.DarkRed); workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(198, 239, 206)); workSheet.Range(alpha[colcount] + rowNum.ToString()).Interior.Color = ToDouble(System.Drawing.Color.FromArgb(204, 128, 51)); TotalOfInactiveOver60 += Convert.ToDecimal(workSheet.Range(alpha[colcount] + rowNum.ToString()).Value); } colcount += 1; } rowNum += 1; colcount = 0; } } int newR = rowNum + 1; workSheet.Columns[1].ColumnWidth = 20; workSheet.Columns[2].ColumnWidth = 12; workSheet.Columns[3].ColumnWidth = 12; workSheet.Columns[4].ColumnWidth = 12; workSheet.Columns[5].ColumnWidth = 12; workSheet.Columns[6].ColumnWidth = 12; workSheet.Columns[7].ColumnWidth = 12; workSheet.Columns[8].ColumnWidth = 12; workSheet.Columns[9].ColumnWidth = 12; workSheet.Columns[10].ColumnWidth = 12; workSheet.Columns[11].ColumnWidth = 12; workSheet.Columns[12].ColumnWidth = 12; workSheet.Columns[13].ColumnWidth = 12; } catch (Exception ex) { workSheet.Range("A100").Value = ex.Message; } }
private static void AddDataRows(Excel.Worksheet sheet) { sheet.Name = "Financial report"; var reportResult = GetData(); var value = new string[reportResult.Length, 5]; for (var row = 0; row < reportResult.Length; row++) { Console.WriteLine("Importing:\n---------------"); for (int col = 0; col < reportResult[row].Length; col++) { value[row, col] = reportResult[row][col]; Console.WriteLine(reportResult[row][col]); } } var range = sheet.Range(sheet.Cells[4, 1], sheet.Cells[reportResult.Length + 3, reportResult[1].Length]); range.Value = value; Console.WriteLine("Ready!!!"); }