How to write custom formatted excel in C# from dataTable

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Export_Excel = RKLib.ExportData;
using System.IO;
using MyExcel = Microsoft.Office.Interop.Excel;

public class FormattedMISReports
{
public static string ExportDataTableToExcel(DataTable dtSource, string ExcelPath, bool blnFirstSheet, string sheetName, int sheetIndex)
{
MyExcel.Application oXL;
MyExcel.Workbook oWB;
MyExcel.Worksheet oSheet;
MyExcel.Range oRange;

List CustomIndex = new List();
CustomIndex.Add(14);
CustomIndex.Add(40);
CustomIndex.Add(41);
CustomIndex.Add(88);
CustomIndex.Add(89);
CustomIndex.Add(90);
CustomIndex.Add(91);
CustomIndex.Add(96);

List NonHypen = new List();
NonHypen.Add(92);
NonHypen.Add(93);
NonHypen.Add(94);
NonHypen.Add(95);

try
{

// Start Excel and get Application object.
oXL = new MyExcel.Application();

// Set some properties
oXL.DisplayAlerts = false;
oXL.Visible = true;

if (blnFirstSheet)
{
// Get a new workbook.
oWB = oXL.Workbooks.Add(Type.Missing);

// Adding extra sheets in the workbook
oWB.Sheets.Add(Type.Missing, oWB.Worksheets[oWB.Worksheets.Count], 7, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

// Activating first sheet in the workbook
oSheet = (MyExcel.Worksheet)oWB.Worksheets[sheetIndex];

oSheet.Activate();

oSheet.Name = sheetName;
}
else
{
// opening existing excel file
oWB = oXL.Workbooks.Open(ExcelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, true, 0, 0);

// Activating first sheet in the workbook
oSheet = (MyExcel.Worksheet)oWB.Worksheets[sheetIndex];

oSheet.Activate();

oSheet.Name = sheetName;
}



// Header Text
oSheet.Cells[1, 1] = "IDS Infotech Ltd.";
oSheet.Cells[2, 1] = "Department: " +sheetName + "";
oSheet.get_Range("A1", "B2").Font.Bold = true;
oSheet.get_Range("A1", "B2").Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);



// Process the DataTable into the Excel
DataTable dt = dtSource;

int startIndex = 5;
int rowCount = 5;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == (startIndex+1))
{
oSheet.Cells[startIndex, i] = dt.Columns[i - 1].ColumnName;
}

// coversion for the zero values
if ((Convert.ToString(dr[i - 1]) == "0.00" || Convert.ToString(dr[i - 1]) == string.Empty) && !NonHypen.Contains(rowCount))
oSheet.Cells[rowCount, i] = "-";
else
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();


// Custom Formatting

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count + 1]); // Additional column for the aggregate-sum
oRange.EntireColumn.AutoFit();

oRange.Borders.LineStyle = MyExcel.XlLineStyle.xlContinuous;
oRange.Borders.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);



oSheet.get_Range(oSheet.Cells[startIndex, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Bold = true;
oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Italic = true;
oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[1, dt.Columns.Count]).Font.Size = 11;

oSheet.get_Range(oSheet.Cells[rowCount, dt.Columns.Count], oSheet.Cells[rowCount, dt.Columns.Count]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;

if (CustomIndex.Contains(rowCount))
{
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Bold = true;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Italic = true;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Size = 11;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, dt.Columns.Count]).Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);
oSheet.get_Range(oSheet.Cells[rowCount, dt.Columns.Count], oSheet.Cells[rowCount, dt.Columns.Count]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;
oSheet.get_Range(oSheet.Cells[rowCount, 1], oSheet.Cells[rowCount, i]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;
}

// End of Custom Formatting

}
}



// array for the excel column heads
string[] array = { "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ" };
int iArrayIndex = 0;

// Applying formulae
for (int i = 2; i < dt.Columns.Count + 2; i++)
{

// Total_Revenue_A
oSheet.get_Range(oSheet.Cells[14, i], oSheet.Cells[14, i]).Formula = "=sum(" + array[iArrayIndex] + "7:" + array[iArrayIndex] + "13)";

// Total_Employ_Cost_B
oSheet.get_Range(oSheet.Cells[40, i], oSheet.Cells[40, i]).Formula = "=sum(" + array[iArrayIndex] + "15:" + array[iArrayIndex] + "39)";

// PerCentage_Employ_Cost_Per_Revenue
oSheet.get_Range(oSheet.Cells[41, i], oSheet.Cells[41, i]).Formula = "=(" + array[iArrayIndex] + "40/" + array[iArrayIndex] + "14)";
oSheet.get_Range(oSheet.Cells[41, i], oSheet.Cells[41, i]).NumberFormat = "##.##%";

// Total_Establis_OtherCost_C
oSheet.get_Range(oSheet.Cells[88, i], oSheet.Cells[88, i]).Formula = "=sum(" + array[iArrayIndex] + "42:" + array[iArrayIndex] + "87)";

// Total_Cost_D_SumOf_B_Plus_C

oSheet.get_Range(oSheet.Cells[89, i], oSheet.Cells[89, i]).Formula = "=(" + array[iArrayIndex] + "88+" + array[iArrayIndex] + "40)";

// Gross_Profit_EBIT
oSheet.get_Range(oSheet.Cells[90, i], oSheet.Cells[90, i]).Formula = "=(" + array[iArrayIndex] + "14-" + array[iArrayIndex] + "89)";

// Gross_Profit_EBIT without CSG cost
oSheet.get_Range(oSheet.Cells[91, i], oSheet.Cells[91, i]).Formula = "=(" + array[iArrayIndex] + "40+" + array[iArrayIndex] + "90+" + array[iArrayIndex] + "88)";

// Netprofit
oSheet.get_Range(oSheet.Cells[96, i], oSheet.Cells[96, i]).Formula = "=(" + array[iArrayIndex] + "14-" + array[iArrayIndex] + "89-" + array[iArrayIndex] + "92-" + array[iArrayIndex] + "93-" + array[iArrayIndex] + "94-" + array[iArrayIndex] + "95)";

if(iArrayIndex < dt.Columns.Count)
iArrayIndex++;
}

// Aggregate Column
for (int k = 7; k <= 97; k++)
{
oSheet.get_Range(oSheet.Cells[k, (dt.Columns.Count + 1)], oSheet.Cells[k, (dt.Columns.Count + 1)]).Formula = "=sum(B" + k + ":"+array[(dt.Columns.Count-2)]+ k + ")";

}

// Set-Header/Column Settings

oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[5, (dt.Columns.Count + 1)]).Value2 = "Total";
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Interior.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Borders.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.LightSteelBlue);
oSheet.get_Range(oSheet.Cells[5, (dt.Columns.Count + 1)], oSheet.Cells[97, (dt.Columns.Count + 1)]).Font.Bold = true;

// Header-Line

oSheet.get_Range(oSheet.Cells[5, 1], oSheet.Cells[5, (dt.Columns.Count + 1)]).Borders.LineStyle = MyExcel.XlLineStyle.xlDouble;


// Report Footer

MyExcel.Range FooterColumn = oSheet.get_Range(oSheet.Cells[100, 1], oSheet.Cells[100, 1]);
FooterColumn.Font.Bold = true;
FooterColumn.Font.Italic = true;
FooterColumn.Font.Color = System.Drawing.ColorTranslator.ToWin32(System.Drawing.Color.DarkBlue);
FooterColumn.Value2 = "Report Generated by MIS_Finance - " + DateTime.Now.ToShortDateString();


// Text-Alignment

oSheet.get_Range(oSheet.Cells[5, 2], oSheet.Cells[96, (dt.Columns.Count + 1)]).HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;


// Save the sheet and close
oSheet = null;
oRange = null;


if (blnFirstSheet)
{
oWB.SaveAs(ExcelPath, MyExcel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
MyExcel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}

else
{
oWB.Save();
}

oWB.Close(Type.Missing, Type.Missing, Type.Missing);
oWB = null;
oXL.Quit();

// No-Errors
return string.Empty;
}
catch (Exception ex)
{
MessageBox.Show("Invalid Operation : \n" + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
DataLayer.ExecuteExcepLogProcedure(ex.ToString());
return "Error " + ex.Message;
}
finally
{

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

}
}
}