Read And Write Spreadsheet Without Any Dependency

Introduction

In the modern cloud world still we are dependent on the thick client components, the spreadsheets are very essential for the current business world, In that we can do ‘n’ number of manipulations.

If we are developing any thick or thin client application with the spreadsheet functionalities, then it’s very difficult to develop without an interop.

Options to develop the spreadsheet functionalities

Microsoft interop
Based on XML
Text stream
ODBC
NPOI

Pros & Cons

Microsoft interop

Thin client application:

The web server should install the Microsoft interop.
The version which you are developing should match with the deployed interop version.

Thick client application:

Microsoft Office is not an open source.
The version which we are developing should match with the installed interop version.

Based on XML:

Here there is no dependency on the thick client.
We cannot achieve the data validation functionalities.
Macro disabled.

Text stream:

We cannot use the formulas and data validation.
No formatting of cells.
Macro disabled.

ODBC:

We can develop end to end functionalities of spreadsheet from ..NET or Java code.
Its a bit painful for the developers while formatting the cells.

NPOI

Open source.
There is no thick client component dependency.
This component is the wrapper class ODBC.
We can achieve all the spreadsheet functionalities.
Macro enabled.

NPOI

I would strongly recommend the NPOI for our Excel and word functionalities.

If we are using thin or thick client application then we have to place the following libraries along with our application libraries:

NPOI.dll
NPOI.OOXML.dll
ICSharpCode.SharpZipLib.dll
NPOI.OpenXmlFormats.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXml4Net.XML
NPOI.OOXML.XML
NPOI.XML

In normal case we need to install the Office interop in the web server, it will create separate cost and version compatibility issue.

For example, if hundreds of thin client application is running in the web server, now we need to install or update the office interop. Then we have to shut down all the applications for a while because the installation requires system restart.

Anyway the NPOI code is open source and we can download the code from the following link and update it as you want.

We can write macros by using NPOI.

Modes	Thick client dependency	Macro	Formatting	Validation	Cost	XLSX	Developer friendly
Interop
Open XML
Text stream
OLEDB/ODBC
NPOI

I’ve given the sample code for the same.

It has many modes if we want work with “.XLS” format then use HSSFWorkbook, if it is “.XLSX” use XSSFWorkbook.

Sample Code

Microsoft interop

Read and write spreadsheet using Microsoft interop.

Reference: Microsoft.Office.Interop.Excel

Read the data from excel book and store it in data table:

Microsoft.Office.Interop.Excel.Workbook oWB = null;
Microsoft.Office.Interop.Excel.Range oRng = null;
Office.Interop.Excel.Application oXL = new Office.Interop.Excel.Application();
oWB = oXL.Workbooks.Open(sUploadFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
foreach(Microsoft.Office.Interop.Excel.Worksheet oSheet in oWB.Sheets)
{
System.Data.DataTable dt = new System.Data.DataTable(oSheet.Name);
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
for(int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
for(int i = 1; i <= iValue; i++)
{
dr = ds.Tables[oSheet.Name].NewRow();
for(int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range) oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables[oSheet.Name].Rows.Add(dr);
}
}

Read the data from dataset and write it in Excel book:

string filepath = "XXX.xlsx";
Microsoft.Office.Interop.Excel.Application ExlApp = new Microsoft.Office.Interop.Excel.Application();
int iCol, iRow, iColVal;
Object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook aBook;
ExlApp = new Microsoft.Office.Interop.Excel.Application();
aBook = ExlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
ExlApp.SheetsInNewWorkbook = 1;
for(iCol = 0; iCol < dt.Columns.Count; iCol++)
{
ExlApp.Cells[1, iCol + 1] = "";
ExlApp.Cells[1, iCol + 1] = dt.Columns[iCol].ColumnName.ToString();
}
for(iRow = 0; iRow < dt.Rows.Count; iRow++)
{
for(iColVal = 0; iColVal < dt.Columns.Count; iColVal++) ExlApp.Cells[iRow + 2, iColVal + 1] = dt.Rows[iRow].ItemArray[iColVal].ToString();
}
ExlApp.ActiveWorkbook.SaveAs(filepath.Trim(), missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
ExlApp.ActiveWorkbook.Close(true, missing, missing);
ExlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp);
aBook = null;
ExlApp = null;

Read and write using open xml:

This only works for Excel 2003 and later versions.

private static string getWorkbookTemplate()
{
var sb = new StringBuilder();
sb.Append("<xml version>\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n");
sb.Append(" <Styles>\r\n <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n <Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
sb.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n <Protection/>\r\n </Style>\r\n <Style ss:ID=\"BoldColumn\">\r\n <Font ");
sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n <Style ss:ID=\"s62\">\r\n <NumberFormat");
sb.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n <Style ss:ID=\"Decimal\">\r\n <NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
sb.Append("<Style ss:ID=\"Integer\">\r\n <NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n <Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n <Style ss:ID=\"s28\">\r\n");
sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\" ss:ReadingOrder=\"LeftToRight\"/>\r\n");
sb.Append("<Font x:CharSet=\"1\" ss:Size=\"9\" ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/> </Style>\r\n");
return sb.ToString();
}

Text stream

Convert DataTable to excel

GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=DataTable.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for(int i = 0; i < GridView1.Rows.Count; i++)
{
GridView1.Rows[i].Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();

OLEDB

Read the excel to DataTable

DataSet ds = new DataSet();
OleDbCommand excelCommand = new OleDbCommand();
OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
OleDbConnection excelConn = new OleDbConnection(excelConnStr);
excelConn.Open();
DataTable dt = new DataTable();
excelCommand = new OleDbCommand("SELECT `PATTERN` as PATTERN, `PLAN` as PLAN FROM [PATTERNS$]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dt);
dt.TableName = "Patterns";
ds.Tables.Add(dt);
return ds;

NPOI

Convert DataTable to excel

XSSFWorkbook hssfwb;
hssfwb = new XSSFWorkbook();
XSSFSheet sh;
int sheetcount = 0;
XSSFFont _style = (XSSFFont) hssfwb.CreateFont();
_style.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
_style.Boldweight = 2;
foreach(DataTable table in ds.Tables)
{
sh = (XSSFSheet) hssfwb.CreateSheet(ds.Tables[sheetcount].TableName);
for(int col = 1; col < table.Columns.Count + 1; col++)
{
if(col == 1) sh.CreateRow(0);
sh.GetRow(0)
.CreateCell(col - 1);
sh.GetRow(0)
.GetCell(col - 1)
.SetCellValue(table.Columns[col - 1].ColumnName);
}
for(int i = 0; i < table.Rows.Count; i++)
{
var r = sh.CreateRow(i + 1);
for(int j = 0; j < table.Columns.Count; j++)
{
sh.GetRow(i + 1)
.CreateCell(j);
sh.GetRow(i + 1)
.GetCell(j)
.SetCellValue(table.Rows[i].ItemArray[j].ToString());
sh.GetRow(i + 1)
.GetCell(j)
.SetCellType(CellType.String);
if(coloringrows != null && coloringrows.Count > 0 && coloringrows.Contains(i))
{
sh.GetRow(i + 1)
.GetCell(j)
.CellStyle.SetFont(_style);
coloringrows.Remove(i);
}
}
}
sheetcount++;
}
using(FileStream file = new FileStream(sPath, FileMode.Create, FileAccess.Write))
{
hssfwb.Write(file);
file.Close();
}
Read the excel to datatable
HSSFWorkbook hssfwb;
using(FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
{
hssfwb = new HSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheet("Arkusz1");
for(int row = 0; row <= sheet.LastRowNum; row++)
{
if(sheet.GetRow(row) != null)
{
// write it in your data table
}
}

Reference

Read And Write Spreadsheet Without Any Dependency

Reactions

2 Comments