[Tips] C# Programming: Reading and Writing Excel Files

Tired for reading and writing Excel files
Tired when searching on google about read and write excel file in C#

If you answer "YES" of all two above questions. Follow my source code, you can easy do it!

Explore My Other Channel for More Cool and Valuable Insights

👉 Youtube Learn Tech Tips

👉 Tiktok

👉 Facebook:

You can download full source code from here

public static void CreateWorkbook(string FileName)
{
 Microsoft.Office.Interop.Excel.Application xl = null;
 Microsoft.Office.Interop.Excel._Workbook wb = null;
 Microsoft.Office.Interop.Excel._Worksheet sheet = null;
 bool SaveChanges = false;
 try
 {
  if (File.Exists(FileName)) 
   File.Delete(FileName); 

  GC.Collect();

  // Create a new instance of Excel from scratch
  xl = new Excel.Application();
  xl.Visible = false;

  // Add one workbook to the instance of Excel
      wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
      wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
      wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
      wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
      wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
      wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

  // Get a reference to the one and only worksheet in our workbook
  //sheet = (Excel._Worksheet)wb.ActiveSheet;
  sheet = (Excel._Worksheet)(wb.Sheets[1]);

  // Fill spreadsheet with sample data //sheet.Name = "Test";

  for (int r = 0; r < 20; r++)
   for (int c = 0; c < 10; c++)
    sheet.Cells[r + 1, c + 1] = 125;

  // set come column heading names
  sheet.Name = "Jan";
  sheet.Cells[1, 1] = "Heading";

  sheet = (Excel._Worksheet)(wb.Sheets[2]);
  sheet.Name = "Feb";
  sheet.Cells[1, 1] = "Heading";

  sheet = (Excel._Worksheet)(wb.Sheets[3]);
  sheet.Name = "Mar";
  sheet.Cells[1, 1] = "Heading";

  sheet = (Excel._Worksheet)(wb.Sheets[4]);
  sheet.Name = "Apr";
  sheet.Cells[1, 1] = "Heading";

  // Let loose control of the Excel instance
  xl.Visible = false;
  xl.UserControl = false;

  // Set a flag saying that all is well and it is ok to 
                // save our changes to a file.
  SaveChanges = true;

  //  Save the file to disk
  wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,
      null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
      false, false, null, null, null);
 }
 catch (Exception err)
 {
  String msg;
  msg = "Error: ";
  msg = String.Concat(msg, err.Message);
  msg = String.Concat(msg, " Line: ");
  msg = String.Concat(msg, err.Source);
  Console.WriteLine(msg);
 }
 finally
 {

  try
  {
   // Repeat xl.Visible and xl.UserControl 
                        // releases just to be sure
   // we didn't error out ahead of time.

   xl.Visible = false;
   xl.UserControl = false;

   // Close the document and avoid user prompts 
                        // to save if our method failed.
   wb.Close(SaveChanges, null, null);
   xl.Workbooks.Close();
  }
  catch { }

  // Gracefully exit out and destroy all COM objects to 
                // avoid hanging instances
  // of Excel.exe whether our method failed or not.

  xl.Quit();

  //if (module != null) { Marshal.ReleaseComObject(module); }
  if (sheet != null) { Marshal.ReleaseComObject(sheet); }
  if (wb != null) { Marshal.ReleaseComObject(wb); }
  if (xl != null) { Marshal.ReleaseComObject(xl); }

  //module = null;
  sheet = null;
  wb = null;
  xl = null;
  GC.Collect();
 }
}


/// <summary>
/// Write data into excel file with path and data
/// </summary>
/// <param name="path">path of excel file</param>
/// <param name="data">data</param>
public static bool WriteDataInExcel(string path, List<string> data)
{
 Excel.Application excelApp = new Excel.Application();
 path = @"D:\Excel.xls";

 object misValue = System.Reflection.Missing.Value;

 excelApp.Workbooks.Open(path, misValue, misValue, misValue, misValue,
  misValue, misValue, misValue, misValue, misValue, misValue,
  misValue, misValue, misValue, misValue);
 int rowIndex = 1; int colIndex = 1;
 excelApp.Cells[rowIndex, colIndex] = "First";
 excelApp.Cells[1, 2] = "First-second";
 excelApp.Cells[2, 3] = "second-third";
 excelApp.Visible = true;

 return true;
}

/// <summary>
/// Read excel file 
/// </summary>
/// <param name="path">path of xac suat file</param>
/// <returns>DataTable : content of file Excel in Sheet one</returns>
public static DataTable ReadExcel_DataTable(string path, string txtSheetName)
{
 string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;";
 connectionString += "Data Source=" + path + ";";
 connectionString += "Extended Properties=" + "\"Excel 12.0;HDR=NO;\"";
 
 // HDR=YES/NO
 // YES : don't read header
 // NO: read header
 // if you don't want to show the header row (first row) 
        //        - use 'HDR=NO' in the string
 // string strSQL = "SELECT * FROM [Sheet1$]";
 // create data table
 
 DataTable dTable = new DataTable();
 OleDbConnection excelConnection = null;
 OleDbCommand dbCommand = null;
 OleDbDataAdapter dataAdapter = null;

 try
 {
  string strSQL = "SELECT * FROM [" + txtSheetName + "$]";
  //string strSQL = "SELECT * FROM [Sheet1$]";

  excelConnection = new OleDbConnection(connectionString);
  excelConnection.Open(); // This code will open excel file.

  dbCommand = new OleDbCommand(strSQL, excelConnection);
  dataAdapter = new OleDbDataAdapter(dbCommand);

  dataAdapter.Fill(dTable);
 }
 catch (Exception ex)
 {
  return dTable;
 }
 finally
 {
  if (dTable != null)
   dTable.Dispose();

  if (dataAdapter != null)
   dataAdapter.Dispose();

  if (dbCommand != null)
   dbCommand.Dispose();

  
  if (excelConnection != null)
   excelConnection.Dispose();

  excelConnection.Close();
  
 }
 return dTable;            
}

The second function

//////////////////////////////////////////////////////////////////////
// The second function support read write excel files
//////////////////////////////////////////////////////////////////////
// Return the worksheet with the given name.
private static Excel.Worksheet FindSheet(Workbook workbook, string sheet_name)
{
 foreach (Excel.Worksheet sheet in workbook.Sheets)
 {
  if (sheet.Name == sheet_name) 
   return sheet;
 }

 return null;
}

/// If you use this, you can 
public static void writeExcelFile(string path, string sheetname, 
                      List<clsanchor_list> lstAnchorID, string txtRoomID)
{
 // Get the Excel application object.
 Microsoft.Office.Interop.Excel.Application excel_app 
                             = new Microsoft.Office.Interop.Excel.Application();

 // Make Excel visible (optional).
 excel_app.Visible = true;

 // Open the workbook.
 Microsoft.Office.Interop.Excel.Workbook workbook 
            = excel_app.Workbooks.Open(path,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing);

 // See if the worksheet already exists.
 string sheet_name = DateTime.Now.ToString(sheetname);

 Microsoft.Office.Interop.Excel.Worksheet sheet 
                                      = FindSheet(workbook, sheet_name);
 if (sheet == null)
 {
  // Add the worksheet at the end.
  sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.Add(Type.Missing, workbook.Sheets[workbook.Sheets.Count], 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
  sheet.Name = DateTime.Now.ToString(sheetname);
 }


 //STT roomid uin  name infolink subscription 
 //STT Room Anchor ID Name idol Facebook Theo Dõi

 // Add some data to individual cells.
 string[] arHeader = new string[] {"ROOM ID", 
     "UNI NAME - ANCHOR ID", 
     "NAME IDOL", 
     "FACEBOOK LINK - infoLink", 
     "THEO DOI - SUBSCRIPTION",                                               
       };

 for (int k = 0; k < arHeader.Length; k++)
  sheet.Cells[1, k + 1] = arHeader[k];

 int row = 2;
 for (int i = 0; i < lstAnchorID.Count; i++)
 {
  sheet.Cells[row, 1] = txtRoomID;
  sheet.Cells[row, 2] = lstAnchorID[i].Uin;
  sheet.Cells[row, 3] = lstAnchorID[i].Name;
  sheet.Cells[row, 4] = lstAnchorID[i].InfoLink;
  sheet.Cells[row, 5] = lstAnchorID[i].Subscription;                       

  row++;
 }

 // Make that range of cells bold and red.
 Microsoft.Office.Interop.Excel.Range header_range 
                                        = sheet.get_Range("A1", "M1");
 header_range.Font.Bold = true;
 header_range.Font.Color 
                    = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
 header_range.Interior.Color 
                    = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
 
 // Close the Excel server.
 excel_app.Quit();
}

/// open excel files with and write, write done will auto saved to hard disk
public static void writeExcelFile1(string path, List<clsanchor_list> lstAnchorList)
{
 Microsoft.Office.Interop.Excel.Application oXL;
 Microsoft.Office.Interop.Excel._Workbook oWB;
 Microsoft.Office.Interop.Excel._Worksheet oSheet;
 Microsoft.Office.Interop.Excel.Range oRng;

 object misvalue = System.Reflection.Missing.Value;
 try
 {
  //Start Excel and get Application object.
  oXL = new Microsoft.Office.Interop.Excel.Application();
  oXL.Visible = true;

  //Get a new workbook.
  oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
  oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

  //Add table headers going cell by cell.
  oSheet.Cells[1, 1] = "First Name";
  oSheet.Cells[1, 2] = "Last Name";
  oSheet.Cells[1, 3] = "Full Name";
  oSheet.Cells[1, 4] = "Salary";

  //Format A1:D1 as bold, vertical alignment = center.
  oSheet.get_Range("A1", "D1").Font.Bold = true;
  oSheet.get_Range("A1", "D1").VerticalAlignment 
                         = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

  // Create an array to multiple values at once.
  string[,] saNames = new string[5, 2];

  saNames[0, 0] = "John";
  saNames[0, 1] = "Smith";
  saNames[1, 0] = "Tom";
  saNames[4, 1] = "Johnson";

  //Fill A2:B6 with an array of values (First and Last Names).
  oSheet.get_Range("A2", "B6").Value2 = saNames;

  //Fill C2:C6 with a relative formula (=A2 & " " & B2).
  oRng = oSheet.get_Range("C2", "C6");
  oRng.Formula = "=A2 & \" \" & B2";

  //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
  oRng = oSheet.get_Range("D2", "D6");
  oRng.Formula = "=RAND()*100000";
  oRng.NumberFormat = "$0.00";

  //AutoFit columns A:D.
  oRng = oSheet.get_Range("A1", "D1");
  oRng.EntireColumn.AutoFit();

  oXL.Visible = false;
  oXL.UserControl = false;
  oWB.SaveAs(path, 
                  Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, 
                  Type.Missing, Type.Missing, false, false, 
                  Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

  oWB.Close();
 }
 catch (Exception ex)
 {
  throw new Exception (ex.Message.ToString());
 }
}

You can download full source code from here

Relative topics:
1. How to read JSON Files
2. How to read txt Files
3. How to read XML Files
4. How to read write file INI Files
5. How to read Excel Files

Thank you for reading this post. I hope you found it helpful and easy to follow. If you have any feedback or questions about C# Programming: Reading and Writing Excel Files , please share them in the comments below. I would love to hear from you and discuss this topic further

✋✋✋✋  Webzone Tech Tips, all things Tech Tips for web development  - I am Zidane, See you next time soon ✋✋✋✋