Add CSV file data as WorkSheet to an Excel file using C# in ASP.Net

Last Reply on Dec 15, 2015 09:25 AM By dharmendr

Posted on Dec 14, 2015 08:51 AM

Hi pls help me with an example where v can create a worksheets dynamically on the same file itself(one file in too many sheets) 

And the sheet name must be dynamic like datetime in it... Kindly help soon.. 

Posted on Dec 15, 2015 09:25 AM Modified on one year ago

Hi vygi0913,

I have created sample that full-fill your requirement.

Code

protected void Page_Load(object sender, EventArgs e)
{
    int i = 0;
    DataTable dt = new DataTable();
    dt.Columns.Add("SourceFile");
    dt.Columns.Add("DestinationFile");
    dt.Columns.Add("Result");
    string result = "Passed";
    foreach (var line in File.ReadAllLines(Server.MapPath("CSVs/Compare.csv")))
    {
        string sourceFilePath = line.Split(';')[0].Split(',')[0];
        string destinationFilePath = line.Split(';')[0].Split(',')[1];
        var sourceFile = File.ReadAllLines(sourceFilePath);
        var destinationFile = File.ReadAllLines(destinationFilePath);
        var compareFile = from ln in sourceFile
                            from ln1 in destinationFile
                            where ln != ln1
                            select ln;
        if (compareFile.ToList().Count > 0)
        {
            result = "Failed";
        }
        dt.Rows.Add(sourceFilePath, destinationFilePath, result);
        i++;
    }

    Microsoft.Office.Interop.Excel.Application excel = default(Microsoft.Office.Interop.Excel.Application);
    Microsoft.Office.Interop.Excel.Workbook workBook = default(Microsoft.Office.Interop.Excel.Workbook);
    excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Visible = true;
    workBook = excel.Workbooks.Open(Server.MapPath("~/CSVs/Book1.xls"));
    var excelSheets = workBook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
    var excelNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.Add(excelSheets[excelSheets.Count], Type.Missing, Type.Missing, Type.Missing);
    excelNewSheet.Name = string.Format("{0}_{1}", DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString()).Replace("/", "_").Replace(":", "_");
    int irow = 1;
    int col = 1;
    foreach (DataColumn column in dt.Columns)
    {
        excelNewSheet.Cells[irow, col] = column.ColumnName;
        col++;
    }
    foreach (DataRow dr in dt.Rows)
    {
        irow += 1;
        col = 0;
        foreach (string c in dr.ItemArray)
        {
            col += 1;
            excelNewSheet.Cells[irow, col] = c;
        }
    }
    workBook.Save();
    workBook.Close();
    excel.Visible = true;
    excel.UserControl = true;
    excelNewSheet = null;
    workBook = null;
    excel = null;
}

Sceernshot