c# – 大数据损坏的excel文件
发布时间：2020-12-15 22:01:27 所属栏目：百科 来源：网络整理
导读：嗨,我正在使用EPPlus创建带有一些数据转储的xslx文件.它可以在本地计算机上以及在Web服务器上完美地处理少量数据. 但我有一个案例,我在数据集中有40,000行.它再次在本地机器上完美运行. 但在服务器上它正在创建文件,当我试图打开该文件时,它显示文件已损坏的
|
嗨,我正在使用EPPlus创建带有一些数据转储的xslx文件.它可以在本地计算机上以及在Web服务器上完美地处理少量数据.
但我有一个案例,我在数据集中有40,000行.它再次在本地机器上完美运行. 但在服务器上它正在创建文件,当我试图打开该文件时,它显示文件已损坏的错误.我尝试用记事本编辑文件,发现它里面有HTML内容. 我正在使用此代码： public static void CreateExcel(string file_Name,DataSet ds)
{
// rowsPerSheet = 50000;
string msg = "";
string Type = "";
using (ExcelPackage pck = new ExcelPackage()) {
//Create the worksheet
ExcelWorksheet ws = default(ExcelWorksheet);
int clCnt = 1;
foreach (DataTable tbl in ds.Tables) {
ws = pck.Workbook.Worksheets.Add(tbl.TableName);
//Load the datatable into the sheet,starting from cell A1. Print the column names on row 1
ws.Cells("A1").LoadFromDataTable(tbl,true);
if (tbl.Rows.Count != 0) {
clCnt = 1;
foreach (DataColumn col in tbl.Columns) {
ws.Column(clCnt).AutoFit();
// format all dates in german format (adjust accordingly)
if (col.DataType.Equals(typeof(System.DateTime))) {
dynamic colNumber = col.Ordinal + 1;
ExcelRange range = ws.Cells(2,colNumber,tbl.Rows.Count + 1,colNumber);
range.Style.Numberformat.Format = "MM/dd/yyyy";
}
if (col.DataType.Equals(typeof(System.Decimal)) || col.DataType.Equals(typeof(System.Double))) {
dynamic colNumber = col.Ordinal + 1;
ExcelRange range = ws.Cells(2,colNumber);
range.Style.Numberformat.Format = "0.00";
}
clCnt += 1;
}
}
}
file_Name = file_Name.Replace(" ","_") + ".xlsx";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition","attachment; filename=" + file_Name + "");
HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
HttpContext.Current.Response.End();
}
}
父功能： private void GenerateReport()
{
string msg = "";
string output = "where";
int count = 0;
string jsscript = "";
string _FID = "";
if ((ddlPortfolio.SelectedValue <= 0 & grvforecast.Rows.Count <= 0)) {
jsscript = "<script>alert('Please select potfolio')</script>";
this.Page.ClientScript.RegisterStartupScript(Page.GetType,"jsclose",jsscript);
} else if ((grvforecast.Rows.Count <= 0)) {
jsscript = "<script>alert('Please add some entry in grid')</script>";
this.Page.ClientScript.RegisterStartupScript(Page.GetType,jsscript);
} else {
if (btnValue.Value == "Cash/GAAP Report") {
_SqlStr = "[USP_CashGaapReport] '";
foreach (GridViewRow row in grvforecast.Rows) {
if ((count < grvforecast.Rows.Count - 1)) {
_SqlStr += "" + grvforecast.Rows(count).Cells(1).Text + ",";
} else {
_SqlStr += "" + grvforecast.Rows(count).Cells(1).Text + "";
}
count = count + 1;
}
_SqlStr += "'";
} else {
if ((btnValue.Value == "Forecast Attribute Report")) {
_SqlStr = "SELECT f.AttributeSetID as AttributeSetID,Attribute_Set.TabName as AttributeSetName FROM Forecast_Account as f INNER JOIN Attribute_Set ON f.AttributeSetID = Attribute_Set.AttributeSetID where ";
} else if ((btnValue.Value == "Forecast Data Report")) {
_SqlStr = "SELECT p.LegalEntityName AS Portfolio,f.Name,c.AccountName,a.RepeatNumber,d.CashGAAP,d.TheDate,SUM(d.Amount) AS Amount,d.LastUpdated,d.UpdatedBy ";
_SqlStr += "FROM dbo.Portfolio AS p INNER JOIN dbo.Forecast AS f ON p.PortfolioID = f.PortfolioID INNER JOIN dbo.Forecast_Account AS a ON f.ForecastID = a.ForecastID ";
_SqlStr += "INNER JOIN dbo.Forecast_Data AS d ON a.ForecastAccountID = d.ForecastAccountID INNER JOIN dbo.CoA AS c ON c.AccountNumber = a.AccountNumber where ";
} else {
// _SqlStr = "SELECT Portfolio,Name,AccountName,CashGAAP,OriginalDate,sum(Amount) as Amount,AccountNumber,AttributeSetName,TheDate,Year"
// _SqlStr &= " FROM (SELECT Portfolio.LegalEntityName AS Portfolio,CoA.AccountName,Forecast_Data.CashGAAP,CONVERT(date,Forecast_Data.TheDate) AS OriginalDate,"
// _SqlStr &= " SUM(Forecast_Data.Amount) AS Amount,CoA.AccountNumber,Attribute_Set.AttributeSetName,'' + CONVERT(varchar,YEAR(Forecast_Data.TheDate))"
// _SqlStr &= " + '-' + CONVERT(varchar,MONTH(Forecast_Data.TheDate)) + '-01' AS TheDate,YEAR(Forecast_Data.TheDate) AS Year,Forecast_Attribute.Value"
// _SqlStr &= " FROM Portfolio INNER JOIN Forecast AS f ON Portfolio.PortfolioID = f.PortfolioID INNER JOIN Forecast_Account ON f.ForecastID = Forecast_Account.ForecastID INNER JOIN Forecast_Data ON"
// _SqlStr &= " Forecast_Account.ForecastAccountID = Forecast_Data.ForecastAccountID INNER JOIN CoA ON CoA.AccountNumber = Forecast_Account.AccountNumber"
// _SqlStr &= " INNER JOIN Attribute_Set ON CoA.AttributeSetID = Attribute_Set.AttributeSetID INNER JOIN Forecast_Attribute ON Forecast_Account.ForecastAccountID = Forecast_Attribute.ForecastAccountID WHERE"
_SqlStr = "SELECT Portfolio,d.AccountNumber as AccountNumber,Year";
_SqlStr += " FROM (SELECT Portfolio.LegalEntityName AS Portfolio,";
_SqlStr += " SUM(Forecast_Data.Amount) AS Amount,YEAR(Forecast_Data.TheDate))";
_SqlStr += " + '-' + CONVERT(varchar,Forecast_Attribute.Value";
_SqlStr += " FROM Portfolio INNER JOIN Forecast AS f ON Portfolio.PortfolioID = f.PortfolioID INNER JOIN Forecast_Account ON f.ForecastID = Forecast_Account.ForecastID INNER JOIN Forecast_Data ON";
_SqlStr += " Forecast_Account.ForecastAccountID = Forecast_Data.ForecastAccountID INNER JOIN CoA ON CoA.AccountNumber = Forecast_Account.AccountNumber";
_SqlStr += " INNER JOIN Attribute_Set ON CoA.AttributeSetID = Attribute_Set.AttributeSetID INNER JOIN Forecast_Attribute ON Forecast_Account.ForecastAccountID = Forecast_Attribute.ForecastAccountID WHERE";
}
foreach (GridViewRow row in grvforecast.Rows) {
if ((count < grvforecast.Rows.Count - 1)) {
_SqlStr += " f.ForecastID=" + grvforecast.Rows(count).Cells(1).Text + " or";
_FID += " a.ForecastID=" + grvforecast.Rows(count).Cells(1).Text + " or";
} else {
_SqlStr += " f.ForecastID=" + grvforecast.Rows(count).Cells(1).Text + " ";
_FID += " a.ForecastID=" + grvforecast.Rows(count).Cells(1).Text + " ";
}
count = count + 1;
}
if ((btnValue.Value == "Forecast Data Report")) {
_SqlStr += "GROUP BY p.LegalEntityName,d.UpdatedBy";
} else if ((btnValue.Value == "Cash/GAAP Report")) {
_SqlStr += " GROUP BY Portfolio.LegalEntityName,Forecast_Data.TheDate,Forecast_Attribute.Value) AS d LEFT OUTER JOIN Vendor ON d.Value = Vendor.VendorName";
_SqlStr += " GROUP BY d.OriginalDate,d.AccountNumber,d.Portfolio,d.Name,d.AccountName,d.AttributeSetName,d.Year,Vendor.VendorName";
// _SqlStr &= " GROUP BY Portfolio.LegalEntityName,Forecast_Attribute.Value) AS d"
// _SqlStr &= " Group BY OriginalDate,Portfolio,Year"
}
}
try {
if ((btnValue.Value != "Forecast Attribute Report")) {
_ds = new DataSet();
_dtTable = myDB.ExecuteDatatable(CommandType.Text,_SqlStr,null);
_dtTable.TableName = btnValue.Value.Replace("_"," ");
_ds.Tables.Add(_dtTable);
} else {
_ds = new DataSet();
_SqlStr += "Group by f.AttributeSetID,Attribute_Set.TabName ";
_dtTable = myDB.ExecuteDatatable(CommandType.Text,null);
foreach (DataRow _dr in _dtTable.Rows) {
_SqlStr = "[USP_Forecast_Attributes] " + _dr["AttributeSetID"].ToString() + ",'" + _FID + "'";
_dtTable = myDB.ExecuteDatatable(CommandType.Text,null);
_dtTable.TableName = _dr["AttributeSetName"].ToString();
_ds.Tables.Add(_dtTable);
}
}
if ((_ds != null)) {
if (DateAndTime.Now.IsDaylightSavingTime()) {
strTime = System.DateTime.UtcNow.AddHours(-5).ToString("_MM_dd_yyyy_hh_mm_tt");
} else {
strTime = System.DateTime.UtcNow.AddHours(-4).ToString("_MM_dd_yyyy_hh_mm_tt");
}
if (btnValue.Value != "Forecast Attribute Report") {
epXL.CreateExcel(btnValue.Value,_ds);
//epXL.ExportToExcel(_dtTable,btnValue.Value)
} else {
epXL.CreateExcel(btnValue.Value,_ds);
// & strTime,_ds)
}
}
} catch (Exception ex) {
jsscript = "<script>alert('Report not generated')</script>";
this.Page.ClientScript.RegisterStartupScript(Page.GetType,jsscript);
}
}
}
编辑： System.IO.IsolatedStorage.IsolatedStorageException: Unable to create mutex. (Exception from HRESULT: 0x80131464) at System.IO.IsolatedStorage.IsolatedStorageFile.Open(String infoFile,String syncName) at System.IO.IsolatedStorage.IsolatedStorageFile.Lock(Boolean& locked) at System.IO.IsolatedStorage.IsolatedStorageFileStream..ctor(String path,FileMode mode,FileAccess access,FileShare share,Int32 bufferSize,IsolatedStorageFile isf) at MS.Internal.IO.Packaging.PackagingUtilities.SafeIsolatedStorageFileStream..ctor(String path,ReliableIsolatedStorageFileFolder folder) at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount,String& fileName) at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary() at MS.Internal.IO.Packaging.SparseMemoryStream.Write(Byte[] buffer,Int32 offset,Int32 count) at MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[] buffer,Int32 count) at MS.Internal.IO.Packaging.CompressStream.Write(Byte[] buffer,Int32 count) at MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[] buffer,Int32 count) at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[] buffer,Int32 count) at System.IO.StreamWriter.Flush(Boolean flushStream,Boolean flushEncoder) at System.IO.StreamWriter.Write(String value) at System.IO.TextWriter.Write(String format,Object arg0) at OfficeOpenXml.ExcelWorksheet.UpdateRowCellData(StreamWriter sw) at OfficeOpenXml.ExcelWorksheet.SaveXml() at OfficeOpenXml.ExcelWorksheet.Save() at OfficeOpenXml.ExcelWorkbook.Save() at OfficeOpenXml.ExcelPackage.GetAsByteArray(Boolean save) at OfficeOpenXml.ExcelPackage.GetAsByteArray() 我无法解决它.请建议. 解决方法
我认为您的网页存在设计问题.至少,快速解决方法是在将文档写入Response之前添加它.
HttpContext.Current.Response.Clear(); 当您在输出中有响应时,表示它尝试呈现页面,在其中,您尝试执行其他操作,在这种情况下编写文件. (以How can I return a pdf from a web request in ASP.NET?为例). 我们必须看到更多的代码才能完全检查,但我想你不会使用HttpHandler,我建议你这样做. public class DownloadFileHandler : IHttpHandler
{
public bool IsReusable
{
get { return true; }
}
public void ProcessRequest(HttpContext context)
{
Response.BinaryWrite(...);
}
}
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |