Create Excel file from C# SQL

Question

I have a ASP.NET Web Application where I am importing an Excel file (.xlsx) and storing the data in a database.

Here is my code to read the Excel file:

private void ImportBulkRegistraion(HttpContext context) 
{
 if (context.Request.Files.Count > 0) 
 {
  HttpFileCollection files = context.Request.Files;
  HttpPostedFile MyFile = files[0];

  DataTable dt = new DataTable();
  using(var excel = new ExcelPackage(MyFile.InputStream)) 
  {
   //loop for all of sheets
   for (int sheetcount = 1; sheetcount <= 1; sheetcount++) 
   {
    ExcelWorksheet worksheet = excel.Workbook.Worksheets[sheetcount];
    ExcelCellAddress startCell = worksheet.Dimension.Start;
    ExcelCellAddress endCell = worksheet.Dimension.End;

    // place all the data into DataTable
    for (int row = startCell.Row; row <= endCell.Row; row++) 
    {
     if (row == 1) 
     {
      //Add columns
      if (sheetcount == 1)
       for (int col = startCell.Column; col <= endCell.Column; col++)
        dt.Columns.Add(worksheet.Cells[row, col].Value.ToString());
     } 
     else
     {
      //Add Rows
      DataRow dr = dt.NewRow();
      int x = 0;
      for (int col = startCell.Column; col <= endCell.Column; col++)
       dr[x++] = worksheet.Cells[row, col].Value;
      dt.Rows.Add(dr);
     }
    }
   }
  }
  ExportUserToDB(dt);
 }
}

//ExportUserToDB(dt); function calls the Stored Procedure where I am inserting the rows into the database.

I want to generate an Excel file where if any of the rows from the input Excel file fails to process, the same rows will be stored in this newly created Excel file (in the same format), which can be fixed manually and then reprocessed again.

For example: If a row has Name column with name as "O'Mac", is not processed by SQL. This is a failed row and should be inserted into newly created Excel file.

Seems very doable, start by adding a try-catch. But have you tried finding a solution for the underlying issue? — Fixation
– Fixation, Commented Feb 19, 2020 at 22:52
It sounds like you're asking us to write your code for you. Stack Overflow is not the place for that. — Ian Kemp - SO dead by AI greed
– Ian Kemp - SO dead by AI greed, Commented Feb 20, 2020 at 6:32

S.ATTA.M · Accepted Answer · 2020-02-20 06:08:26Z

1

Create a DataTable object dedicated for exceptions e.g.:

DataTable dtException = new DataTable();
Add all those DataRows in dtException which gets fail
Write a method e.g. WriteException() and pass dtException to write all exceptions in Excel sheet:

        private void WriteException(DataTable dt)
        {

            using (ExcelPackage excel = new ExcelPackage())
            {
                excel.Workbook.Worksheets.Add("Exceptions");

            var worksheet = excel.Workbook.Worksheets["Exceptions"];

            worksheet.Cells["A1"].LoadFromDataTable(dt,false);

            FileInfo excelFile = new FileInfo(@"C:\Temp\AllExceptions.xlsx");
            excel.SaveAs(excelFile);
        }
    }

answered Feb 20, 2020 at 6:08

S.ATTA.M

5076 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Excel file from C# SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related