How to Make Excel Spreadsheets [.XLS &.XLSX] in C#

How to Make Excel Spreadsheets [.XLS &.XLSX] in C#

There's a library called ExcelLibrary. It's a free, open source library posted on Google Code. It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats. You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files).

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU Library General Public License (LGPL)

Here's some example code for ExcelLibrary:

//create new xls file
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);

// open xls file
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];

 // traverse cells
 foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
 {
     dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
 }

 // traverse rows by Index
 for (int rowIndex = sheet.Cells.FirstRowIndex;
        rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
 {
     Row row = sheet.Cells.GetRow(rowIndex);
     for (int colIndex = row.FirstColIndex;
        colIndex <= row.LastColIndex; colIndex++)
     {
         Cell cell = row.GetCell(colIndex);
     }
 }

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

Credits : Mike Webb

3 comments

It's A Really Useful Information, Thank you for sharing....
Advanced Digital Marketing Course in Bangalore

Reply

I also use eksel, I like this program with their cells and columns, and still there it is convenient to select columns

Reply

CIITN is the best SAP training and placement Institute in noida. Contestant will analyze in keeping with modern-day enterprise trends by way of certified and industry experts & SAP training Institute in Noida. They provide a 100% placement help with the best training of IT cousrses.

If you are looking best sap training in Noida then you can join CIITN. One of the premier training and development institute for SAP Training Program in Noida. The company provides 6 weeks/ 6 Months Training for SAP. They provide training in all SAP Modules like SAP HANA,SAP B1, SAP FICO etc. Just come for free demo class and then join SAP and move up in your career .

SAP Training Institute in Noida
sap training in noida
sap institute in noida
sap course in noida
best sap training institute in noida

Reply

Post a Comment