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

1 comments:

Woah, that's actually very interesting! Helped me a lot with my project for write paper for me
thanks!!

Reply

Post a Comment