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.

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. — Grant H., Mar 05 '12 at 15:48
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. — Baxter, Mar 05 '12 at 15:59

Antonio Bakula · Accepted Answer · 2015-10-08T15:24:26.933

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;
    }
  }

score 3 · Answer 2 · answered Mar 05 '12 at 15:48

3

http://msdn.microsoft.com/en-us/library/cc850837.aspx

answered Mar 05 '12 at 15:48

Scroog1

3,539
21
26

Doesn't that require Excel (2010)? – Hans Keﬆing Mar 05 '12 at 16:05

score 2 · Answer 3 · edited Oct 29 '18 at 11:31

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

    }
}

C# create/modify/read .xlsx files

3 Answers3

Linked