Background
Break News
How to add local font to Tailwind Css and NextJS? - Tutorial Design Pattern? - Blockchain Technology, How to create own Bitcoin virtual currency - Zustand mordern management state - Design Pattern - Flyweight Pattern? - Docker Full training Topic

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

Wednesday 13 April 2016
|
Read: Completed in minutes

[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#

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!






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 ✋✋✋✋

๐Ÿ™‡๐Ÿผ๐Ÿ™‡๐Ÿผ We Appreciate Your Comments and Suggestions - Webzone, all things Tech Tips web development
Popular Webzone Tech Tips topic maybe you will be like it - by Webzone Tech Tips - Zidane
As a student, I found Blogspot very useful when I joined in 2014. I have been a developer for years . To give back and share what I learned, I started Webzone, a blog with tech tips. You can also search for tech tips zidane on Google and find my helpful posts. Love you all,

I am glad you visited my blog. I hope you find it useful for learning tech tips and webzone tricks. If you have any technical issues, feel free to browse my posts and see if they can help you solve them. You can also leave a comment or contact me if you need more assistance. Here is my blog address: https://learn-tech-tips.blogspot.com.

My blog where I share my passion for web development, webzone design, and tech tips. You will find tutorials on how to build websites from scratch, using hot trends frameworks like nestjs, nextjs, cakephp, devops, docker, and more. You will also learn how to fix common bugs on development, like a mini stackoverflow. Plus, you will discover how to easily learn programming languages such as PHP (CAKEPHP, LARAVEL), C#, C++, Web(HTML, CSS, javascript), and other useful things like Office (Excel, Photoshop). I hope you enjoy my blog and find it helpful for your projects. :)

Thanks and Best Regards!
Follow me on Tiktok @learntechtips and send me a direct message. I will be happy to chat with you.
Webzone - Zidane (huuvi168@gmail.com)
I'm developer, I like code, I like to learn new technology and want to be friend with people for learn each other
I'm a developer who loves coding, learning new technologies, and making friends with people who share the same passion. I have been a full stack developer since 2015, with more than years of experience in web development.
Copyright @2022(November) Version 1.0.0 - By Webzone, all things Tech Tips for Web Development Zidane
https://learn-tech-tips.blogspot.com