【Coolite】Coolite优化导出Excel文件实现代码-导出Excel

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【Coolite优化导出Excel文件实现代码】，下面是详细的讲解！

Coolite优化导出Excel文件实现代码

protected void Sdate_SubmitData(object sender, StoreSubmitDataEventArgs e)
{
String json=HToFile.Value.ToString();
if (!String.IsNullOrEmpty(json))
{
ExportDate exportDate=JSON.Deserialize<ExportDate>(json);
if (exportDate.dataCount > 0)
{
if (exportDate.Dates==null || exportDate.Dates.Length < exportDate.dataCount)
{
//如数据超过500条这重新查询数据导出
}
switch (exportDate.format)
{
case "xls":
GetToExcel(exportDate);
break;
case "pdf":
......
break;
}
}
}
}
public static void GetToExcel(ExportDate exportDate)
{
if (exportDate.Dates==null) { return; }
HttpContext context=HttpContext.Current;
if (context !=null)
{
String rowid="";
StringBuilder sb=new StringBuilder();
int columns=0;
foreach (GridColumnInfo item in exportDate.GridColumnInfos)
{
if (!item.hidden || item.BGroup)
{
columns++;
}
}
#region 头部
sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sb.Append("<?mso-application progid=\"Excel.Sheet\"?>");
sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
sb.Append(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
sb.Append(" <Version>12.00</Version>");
sb.Append(" </DocumentProperties>");
sb.Append(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">");
sb.Append(" <RemovePersonalInformation/>");
sb.Append(" </OfficeDocumentSettings>");
sb.Append(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sb.Append(" <WindowHeight>11640</WindowHeight>");
sb.Append(" <WindowWidth>19200</WindowWidth>");
sb.Append(" <WindowTopX>0</WindowTopX>");
sb.Append(" <WindowTopY>90</WindowTopY>");
sb.Append(" <ProtectStructure>False</ProtectStructure>");
sb.Append(" <ProtectWindows>False</ProtectWindows>");
sb.Append(" </ExcelWorkbook>");
#region 样式
sb.Append("<Styles>");
sb.Append("<Style ss:ID=\"Default\">");
sb.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\" />");
sb.Append("<Font ss:FontName=\"宋体\" ss:Size=\"11\" />");
//sb.Append("<Borders>");
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
//sb.Append("</Borders>");
sb.Append("<Interior />");
sb.Append("<NumberFormat />");
sb.Append("<Protection />");
sb.Append("</Style>");
sb.Append("<Style ss:ID=\"title\">");
sb.Append("<Borders />");
sb.Append("<Font ss:Size=\"16\" ss:Bold=\"1\" />");
sb.Append("<Alignment ss:WrapText=\"1\" ss:Vertical=\"Center\" ss:Horizontal=\"Center\" />");
sb.Append("<NumberFormat ss:Format=\"@\" />");
sb.Append("</Style>");
sb.Append("<Style ss:ID=\"headercell\">");
sb.Append("<Font ss:Bold=\"1\" ss:Size=\"12\" />");
sb.Append("<Alignment ss:WrapText=\"1\" ss:Horizontal=\"Center\" />");
sb.Append("<Interior ss:Pattern=\"Solid\" ss:Color=\"#F2F2F2\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:ID=\"even\">");
sb.Append("<Interior ss:Pattern=\"Solid\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evendate\">");
sb.Append("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evenint\">");
sb.Append("<NumberFormat ss:Format=\"0\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evenfloat\">");
sb.Append("<NumberFormat ss:Format=\"0.00\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:ID=\"odd\">");
sb.Append("<Interior ss:Pattern=\"Solid\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"odddate\">");
sb.Append("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"oddint\">");
sb.Append("<NumberFormat ss:Format=\"0\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"oddfloat\">");
sb.Append("<NumberFormat ss:Format=\"0.00\" />");
sb.Append("<Borders>");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
sb.Append("</Borders>");
sb.Append("</Style>");
sb.Append("</Styles>");
#endregion
sb.AppendFormat("<Worksheet ss:Name=\"{0}\">", exportDate.title);
sb.AppendFormat("<Table x:FullRows=\"1\" x:FullColumns=\"1\" ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\">", columns, exportDate.Dates.Length + 2);
#endregion
//表列宽度
int ColumnWidthsZ=0;
foreach (GridColumnInfo item in exportDate.GridColumnInfos)
{
if (!item.hidden || item.BGroup)
{
ColumnWidthsZ +=item.width;
sb.AppendFormat("<Column ss:AutoFitWidth=\"1\" ss:Width=\"{0}\" />", item.width);
}
}
//标题
sb.Append("<Row ss:Height=\"28\">");
sb.AppendFormat("<Cell ss:StyleID=\"title\" ss:MergeAcross=\"{0}\">", columns - 1);
sb.AppendFormat("<Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" />", exportDate.title);
sb.Append("</Cell>");
sb.Append("</Row>");
//表头
sb.Append("<Row ss:AutoFitHeight=\"1\">");
foreach (GridColumnInfo item in exportDate.GridColumnInfos)
{
if (!item.hidden || item.BGroup)
{
sb.AppendFormat("<Cell ss:StyleID=\"headercell\"><Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" /></Cell>", item.header);
}
}
sb.Append("</Row>");
//数据
int i=0;
string cellClass="";
foreach (Dictionary<string, string> row in exportDate.Dates)
{
i++;
cellClass=((i & 1)==0) ? "odd" : "even";
sb.Append("<Row>");
foreach (GridColumnInfo item in exportDate.GridColumnInfos)
{
rowid=item.id;
if (string.IsNullOrEmpty(rowid)) rowid=item.dataIndex;
if (!String.IsNullOrEmpty(rowid) && (!item.hidden || item.BGroup) && row.ContainsKey(rowid))
{
sb.AppendFormat("<Cell ss:StyleID=\"{0}{1}\"><Data ss:Type=\"{2}\">{3}</Data></Cell>",
cellClass, exportDate.GetStyleID(item.recordFieldType), exportDate.GetDataType(item.recordFieldType), row[rowid]);
}
}
sb.Append("</Row>");
}
#region 尾部
sb.Append("</Table>");
sb.Append("<WorksheetOptions>");
sb.Append("<PageSetup>");
sb.Append("<Layout x:CenterHorizontal=\"1\" x:Orientation=\"Landscape\" />");
sb.Append("<Footer x:Data=\"Page &P of &N\" x:Margin=\"0.5\" />");
sb.Append("<PageMargins x:Top=\"0.5\" x:Right=\"0.5\" x:Left=\"0.5\" x:Bottom=\"0.8\" />");
sb.Append("</PageSetup>");
sb.Append("<FitToPage />");
sb.Append("<Print>");
sb.Append("<PrintErrors>Blank</PrintErrors>");
sb.Append("<FitWidth>1</FitWidth>");
sb.Append("<FitHeight>32767</FitHeight>");
sb.Append("<ValidPrinterInfo />");
sb.Append("<VerticalResolution>600</VerticalResolution>");
sb.Append("</Print>");
sb.Append("<Selected />");
sb.Append("<DoNotDisplayGridlines />");
sb.Append("<ProtectObjects>False</ProtectObjects>");
sb.Append("<ProtectScenarios>False</ProtectScenarios>");
sb.Append("</WorksheetOptions>");
sb.Append("</Worksheet></Workbook>");
#endregion
context.Response.Clear();
if (context.Request.Browser.Browser !="IE")
context.Response.AppendHeader("Content-Disposition", String.Format("attachment; filename=\"{0}.xls\"", exportDate.fileName));
else context.Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode(exportDate.fileName)));
context.Response.ContentType="application/excel";
context.Response.Write(sb.ToString());
context.Response.End();
}
}

关于Coolite优化导出Excel文件实现代码的用户互动如下：

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细