Geek Answers Handbook

C # create / modify / read .xlsx files

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 the user a 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 a file on the server before it can be sent to the user. Does anyone know about this?

I found EPPlus http://epplus.codeplex.com , but I'm not sure if this requires creating a file on the server before it can be sent to the user?

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

+3
source share
3 answers

With EPPlus, you don’t need to create a file, you can do everything with streams, here is an example of an ashx ASPX handler that will export a datatable to an excel file and return it 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; } }
+15
source

Try using this code to export data to excel, can it 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) { } }
+2
source

More articles:


All Articles