C# 创建/修改/读取 .xlsx 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9569401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# create/modify/read .xlsx files
提问by Baxter
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.
我正在寻找一种在 C# 中创建、修改、读取 .xlsx 文件的方法，而无需在提供给用户下载之前在服务器上安装 Excel 或创建文件。
I found NPOI http://npoi.codeplex.com/which looks great but supports .xls not .xlsx
我发现 NPOI http://npoi.codeplex.com/看起来不错但支持 .xls 而不是 .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?
我发现 ExcelPackage http://excelpackage.codeplex.com/看起来不错，但在将文件发送给用户之前在服务器上创建文件有额外的开销。有谁知道解决这个问题的方法？
I found EPPlus http://epplus.codeplex.combut I am not not certain if this requires creation of a file on the server before it can be sent to the user?
我找到了 EPPlus http://epplus.codeplex.com，但我不确定这是否需要在服务器上创建一个文件才能发送给用户？
I am pretty new to this so any guidance/examples etc., would be very much appreciated.
我对此很陌生，因此非常感谢任何指导/示例等。
采纳答案by Antonio Bakula
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 :
使用 EPPlus 不需要创建文件，您可以使用流完成所有操作，以下是 ASP.NET ashx 处理程序的示例，它将数据表导出到 excel 文件并将其提供给客户端：
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;
}
}
回答by Scroog1
回答by Naveen Desosha
Try to use this code to export the data to excel, may it ll help
尝试使用此代码将数据导出到excel，可能会有所帮助
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)
{
}
}
