C# create/modify/read .xlsx files

Question

I am looking for a way to create, modify, read .xlsx files in C# without installing Excel or creating files on the server before giving to the user to download.

I found NPOI http://npoi.codeplex.com/ which looks great but supports .xls not .xlsx

I found ExcelPackage http://excelpackage.codeplex.com/ which looks great but has the additional overhead of creating the file on the server before it can be sent to the user. Does anyone know of a way around this?

I found EPPlus http://epplus.codeplex.com but I am not not certain if this requires creation of a file on the server before it can be sent to the user?

I am pretty new to this so any guidance/examples etc., would be very much appreciated.

Gelatinous

When you say creation of the file on the client...are you using a silverlight front end...or a winforms app? Otherwise, I'm not sure any approach will really let you create the file client side. – Glycol

I don't want to create the file on the client. I want to create the file in memory on the server and then stream the file to the client. – Gelatinous

Answer 1

With EPPlus it's not required to create file, you can do all with streams, here is an example of ASP.NET ashx handler that will export datatable into excel file and serve it back to the client :

  public class GetExcel : IHttpHandler
  {
    public void ProcessRequest(HttpContext context)
    {
      var dt = DBServer.GetDataTable("select * from table");
      var ms = GetExcel.DataTableToExcelXlsx(dt, "Sheet1");
      ms.WriteTo(context.Response.OutputStream);
      context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
      context.Response.AddHeader("Content-Disposition", "attachment;filename=EasyEditCmsGridData.xlsx");
      context.Response.StatusCode = 200;
      context.Response.End();   
    }

    public bool IsReusable
    {
      get
      {
        return false;
      }
    }

    public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
    {
      var result = new MemoryStream();
      var pack = new ExcelPackage();
      var ws = pack.Workbook.Worksheets.Add(sheetName);

      int col = 1;
      int row = 1;
      foreach (DataRow rw in table.Rows)
      {
        foreach (DataColumn cl in table.Columns)
        {
          if (rw[cl.ColumnName] != DBNull.Value)
            ws.Cells[row, col].Value = rw[cl.ColumnName].ToString();
          col++;
        }
        row++;
        col = 1;
      }
      pack.SaveAs(result);
      return result;
    }
  }

Society

How would you utilize it? Also, what if there are multiple datatables that I would like as multiple sheets within the Excel file? – Lundquist

Answer 2

Try to use this code to export the data to excel, may it ll help

public static void DataSetsToExcel(DataSet dataSet, string filepath)
{
    try
    {
        string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
        string tablename = "";
        DataTable dt = new DataTable();
        foreach (System.Data.DataTable dataTable in dataSet.Tables)
        {
            dt = dataTable;
            tablename = dataTable.TableName;
            using (OleDbConnection con = new OleDbConnection(connString))
            {
                con.Open();
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");
                strSQL.Append("(");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
                }
                strSQL = strSQL.Remove(strSQL.Length - 1, 1);
                strSQL.Append(")");

                OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                cmd.ExecuteNonQuery();

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strSQL.Clear();
                    StringBuilder strfield = new StringBuilder();
                    StringBuilder strvalue = new StringBuilder();
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        strfield.Append("[" + dt.Columns[j].ColumnName + "]");
                        strvalue.Append("'" + dt.Rows[i][j].ToString().Replace("'", "''") + "'");
                        if (j != dt.Columns.Count - 1)
                        {
                            strfield.Append(",");
                            strvalue.Append(",");
                        }
                        else
                        {
                        }
                    }
                    if (strvalue.ToString().Contains("<br/>"))
                    {
                        strvalue = strvalue.Replace("<br/>", Environment.NewLine);
                    }
                    cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ")
                        .Append(strfield.ToString())
                        .Append(") values (").Append(strvalue).Append(")").ToString();
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
        }
    }
    catch (Exception ex)
    {

    }
}

Impanel

Answer 3

Update: As of November 10, 2023, NPOI does support XLSX.

The question was asked on March 5, 2023, and the commit that confirms the XLSX support is from January 3, 2013. Another proof is the SO thread c# - Does NPOI have support to .xlsx format?.

SIDE NOTE
NPOI uses Microsoft's Open XML SDK for Office behind the scenes.

Romany