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

Pros & Cons

Microsoft interop

Thin client application:

Thick client application:

Based on XML:

Text stream:

ODBC:

NPOI

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:

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:

  1. Microsoft.Office.Interop.Excel.Workbook oWB = null;
  2. Microsoft.Office.Interop.Excel.Range oRng = null;
  3. Office.Interop.Excel.Application oXL = new Office.Interop.Excel.Application();
  4. 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);
  5. foreach(Microsoft.Office.Interop.Excel.Worksheet oSheet in oWB.Sheets)
  6. {
  7. System.Data.DataTable dt = new System.Data.DataTable(oSheet.Name);
  8. ds.Tables.Add(dt);
  9. DataRow dr;
  10. StringBuilder sb = new StringBuilder();
  11. int jValue = oSheet.UsedRange.Cells.Columns.Count;
  12. int iValue = oSheet.UsedRange.Cells.Rows.Count;
  13. for(int j = 1; j <= jValue; j++)
  14. {
  15. dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
  16. }
  17. for(int i = 1; i <= iValue; i++)
  18. {
  19. dr = ds.Tables[oSheet.Name].NewRow();
  20. for(int j = 1; j <= jValue; j++)
  21. {
  22. oRng = (Microsoft.Office.Interop.Excel.Range) oSheet.Cells[i, j];
  23. string strValue = oRng.Text.ToString();
  24. dr["column" + j] = strValue;
  25. }
  26. ds.Tables[oSheet.Name].Rows.Add(dr);
  27. }
  28. }
Read the data from dataset and write it in Excel book:
  1. string filepath = "XXX.xlsx";
  2. Microsoft.Office.Interop.Excel.Application ExlApp = new Microsoft.Office.Interop.Excel.Application();
  3. int iCol, iRow, iColVal;
  4. Object missing = System.Reflection.Missing.Value;
  5. Microsoft.Office.Interop.Excel.Workbook aBook;
  6. ExlApp = new Microsoft.Office.Interop.Excel.Application();
  7. aBook = ExlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
  8. ExlApp.SheetsInNewWorkbook = 1;
  9. for(iCol = 0; iCol < dt.Columns.Count; iCol++)
  10. {
  11. ExlApp.Cells[1, iCol + 1] = "";
  12. ExlApp.Cells[1, iCol + 1] = dt.Columns[iCol].ColumnName.ToString();
  13. }
  14. for(iRow = 0; iRow < dt.Rows.Count; iRow++)
  15. {
  16. for(iColVal = 0; iColVal < dt.Columns.Count; iColVal++) ExlApp.Cells[iRow + 2, iColVal + 1] = dt.Rows[iRow].ItemArray[iColVal].ToString();
  17. }
  18. ExlApp.ActiveWorkbook.SaveAs(filepath.Trim(), missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
  19. ExlApp.ActiveWorkbook.Close(true, missing, missing);
  20. ExlApp.Quit();
  21. System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp);
  22. aBook = null;
  23. ExlApp = null;
Read and write using open xml:

This only works for Excel 2003 and later versions.
  1. private static string getWorkbookTemplate()
  2. {
  3. var sb = new StringBuilder();
  4. sb.Append("<xml version>\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
  5. 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");
  6. sb.Append(" <Styles>\r\n <Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n <Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
  7. 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 ");
  8. sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n <Style ss:ID=\"s62\">\r\n <NumberFormat");
  9. 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 ");
  10. sb.Append("<Style ss:ID=\"Integer\">\r\n <NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n <Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
  11. sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n <Style ss:ID=\"s28\">\r\n");
  12. sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\" ss:ReadingOrder=\"LeftToRight\"/>\r\n");
  13. sb.Append("<Font x:CharSet=\"1\" ss:Size=\"9\" ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
  14. sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/> </Style>\r\n");
  15. return sb.ToString();
  16. }
Text stream
Convert DataTable to excel
  1. GridView GridView1 = new GridView();
  2. GridView1.AllowPaging = false;
  3. GridView1.DataSource = dt;
  4. GridView1.DataBind();
  5. Response.Clear();
  6. Response.Buffer = true;
  7. Response.AddHeader("content-disposition", "attachment;filename=DataTable.xls");
  8. Response.Charset = "";
  9. Response.ContentType = "application/vnd.ms-excel";
  10. StringWriter sw = new StringWriter();
  11. HtmlTextWriter hw = new HtmlTextWriter(sw);
  12. for(int i = 0; i < GridView1.Rows.Count; i++)
  13. {
  14. GridView1.Rows[i].Attributes.Add("class", "textmode");
  15. }
  16. GridView1.RenderControl(hw);
  17. string style = @"<style> .textmode { mso-number-format:\@; } </style>";
  18. Response.Write(style);
  19. Response.Output.Write(sw.ToString());
  20. Response.Flush();
  21. Response.End();
OLEDB

Read the excel to DataTable
  1. DataSet ds = new DataSet();
  2. OleDbCommand excelCommand = new OleDbCommand();
  3. OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
  4. string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
  5. OleDbConnection excelConn = new OleDbConnection(excelConnStr);
  6. excelConn.Open();
  7. DataTable dt = new DataTable();
  8. excelCommand = new OleDbCommand("SELECT `PATTERN` as PATTERN, `PLAN` as PLAN FROM [PATTERNS$]", excelConn);
  9. excelDataAdapter.SelectCommand = excelCommand;
  10. excelDataAdapter.Fill(dt);
  11. dt.TableName = "Patterns";
  12. ds.Tables.Add(dt);
  13. return ds;
NPOI

Convert DataTable to excel
  1. XSSFWorkbook hssfwb;
  2. hssfwb = new XSSFWorkbook();
  3. XSSFSheet sh;
  4. int sheetcount = 0;
  5. XSSFFont _style = (XSSFFont) hssfwb.CreateFont();
  6. _style.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
  7. _style.Boldweight = 2;
  8. foreach(DataTable table in ds.Tables)
  9. {
  10. sh = (XSSFSheet) hssfwb.CreateSheet(ds.Tables[sheetcount].TableName);
  11. for(int col = 1; col < table.Columns.Count + 1; col++)
  12. {
  13. if(col == 1) sh.CreateRow(0);
  14. sh.GetRow(0)
  15. .CreateCell(col - 1);
  16. sh.GetRow(0)
  17. .GetCell(col - 1)
  18. .SetCellValue(table.Columns[col - 1].ColumnName);
  19. }
  20. for(int i = 0; i < table.Rows.Count; i++)
  21. {
  22. var r = sh.CreateRow(i + 1);
  23. for(int j = 0; j < table.Columns.Count; j++)
  24. {
  25. sh.GetRow(i + 1)
  26. .CreateCell(j);
  27. sh.GetRow(i + 1)
  28. .GetCell(j)
  29. .SetCellValue(table.Rows[i].ItemArray[j].ToString());
  30. sh.GetRow(i + 1)
  31. .GetCell(j)
  32. .SetCellType(CellType.String);
  33. if(coloringrows != null && coloringrows.Count > 0 && coloringrows.Contains(i))
  34. {
  35. sh.GetRow(i + 1)
  36. .GetCell(j)
  37. .CellStyle.SetFont(_style);
  38. coloringrows.Remove(i);
  39. }
  40. }
  41. }
  42. sheetcount++;
  43. }
  44. using(FileStream file = new FileStream(sPath, FileMode.Create, FileAccess.Write))
  45. {
  46. hssfwb.Write(file);
  47. file.Close();
  48. }
  49. Read the excel to datatable
  50. HSSFWorkbook hssfwb;
  51. using(FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
  52. {
  53. hssfwb = new HSSFWorkbook(file);
  54. }
  55. ISheet sheet = hssfwb.GetSheet("Arkusz1");
  56. for(int row = 0; row <= sheet.LastRowNum; row++)
  57. {
  58. if(sheet.GetRow(row) != null)
  59. {
  60. // write it in your data table
  61. }
  62. }
Reference