Tag Archives: Create Excel Programmatically

Create Excel Report Programmatically Without Installing MS Excel. Asp.Net & Win Form C# Using WebService Example

Assuming you only have open source spreadsheet software i.e OpenOffice.org Calc and you want to make Excel report file programmtically.
You dont have MS Office installed in your computer or server. Sure you can do it using ExcelLibrary library from Google Apps.

In order to try examples in this blog post, please download ExcelLibrary library from this url https://code.google.com/p/excellibrary/downloads/list

Here, I give examples implementing it by creating a WebService so that Asp.Net Website or Win From C# can use this WebService.
This makes ExcelLibrary file only have to copy in WebService’s server without distribute it to local / client box.

First of all we need to know about basic of ExcelLibrary usage. Taken from https://code.google.com/p/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);
     }
 }

Continue reading