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!!!");
}