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)
- {
-
- }
- }
Reference