SoFunction
Updated on 2025-03-09

Coolite optimized export of Excel file implementation code


protected void Sdate_SubmitData(object sender, StoreSubmitDataEventArgs e)
{
String json = ();
if (!(json))
{
ExportDate exportDate = <ExportDate>(json);
if ( > 0)
{
if ( == null || < )
{
//If there are more than 500 data, re-query the data export
}
switch ()
{
case "xls":
GetToExcel(exportDate);
break;
case "pdf":
......
break;
}
}
}
}
public static void GetToExcel(ExportDate exportDate)
{
if ( == null) { return; }
HttpContext context = ;
if (context != null)
{
String rowid = "";
StringBuilder sb = new StringBuilder();
int columns = 0;
foreach (GridColumnInfo item in )
{
if (! || )
{
columns++;
}
}
#region Head
("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
("<?mso-application progid=\"\"?>");
("<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:///TR/REC-html40\">");
(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
(" <Version>12.00</Version>");
(" </DocumentProperties>");
(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">");
(" <RemovePersonalInformation/>");
(" </OfficeDocumentSettings>");
(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
(" <WindowHeight>11640</WindowHeight>");
(" <WindowWidth>19200</WindowWidth>");
(" <WindowTopX>0</WindowTopX>");
(" <WindowTopY>90</WindowTopY>");
(" <ProtectStructure>False</ProtectStructure>");
(" <ProtectWindows>False</ProtectWindows>");
(" </ExcelWorkbook>");
#region style
("<Styles>");
("<Style ss:ID=\"Default\">");
("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\" />");
("<Font ss:FontName=\"An An\" ss:Size=\"11\" />");
//("<Borders>");
//("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
//("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
//("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
//("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
//("</Borders>");
("<Interior />");
("<NumberFormat />");
("<Protection />");
("</Style>");
("<Style ss:ID=\"title\">");
("<Borders />");
("<Font ss:Size=\"16\" ss:Bold=\"1\" />");
("<Alignment ss:WrapText=\"1\" ss:Vertical=\"Center\" ss:Horizontal=\"Center\" />");
("<NumberFormat ss:Format=\"@\" />");
("</Style>");
("<Style ss:ID=\"headercell\">");
("<Font ss:Bold=\"1\" ss:Size=\"12\" />");
("<Alignment ss:WrapText=\"1\" ss:Horizontal=\"Center\" />");
("<Interior ss:Pattern=\"Solid\" ss:Color=\"#F2F2F2\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:ID=\"even\">");
("<Interior ss:Pattern=\"Solid\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"even\" ss:ID=\"evendate\">");
("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"even\" ss:ID=\"evenint\">");
("<NumberFormat ss:Format=\"0\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"even\" ss:ID=\"evenfloat\">");
("<NumberFormat ss:Format=\"0.00\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:ID=\"odd\">");
("<Interior ss:Pattern=\"Solid\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"odd\" ss:ID=\"odddate\">");
("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"odd\" ss:ID=\"oddint\">");
("<NumberFormat ss:Format=\"0\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("<Style ss:Parent=\"odd\" ss:ID=\"oddfloat\">");
("<NumberFormat ss:Format=\"0.00\" />");
("<Borders>");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />");
("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />");
("</Borders>");
("</Style>");
("</Styles>");
#endregion
("<Worksheet ss:Name=\"{0}\">", );
("<Table x:FullRows=\"1\" x:FullColumns=\"1\" ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\">", columns, + 2);
#endregion
//Table column width
int ColumnWidthsZ = 0;
foreach (GridColumnInfo item in )
{
if (! || )
{
ColumnWidthsZ += ;
("<Column ss:AutoFitWidth=\"1\" ss:Width=\"{0}\" />", );
}
}
//title
("<Row ss:Height=\"28\">");
("<Cell ss:StyleID=\"title\" ss:MergeAcross=\"{0}\">", columns - 1);
("<Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" />", );
("</Cell>");
("</Row>");
//Table header
("<Row ss:AutoFitHeight=\"1\">");
foreach (GridColumnInfo item in )
{
if (! || )
{
("<Cell ss:StyleID=\"headercell\"><Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" /></Cell>", );
}
}
("</Row>");
//data
int i = 0;
string cellClass = "";
foreach (Dictionary<string, string> row in )
{
i++;
cellClass = ((i & 1) == 0) ? "odd" : "even";
("<Row>");
foreach (GridColumnInfo item in )
{
rowid = ;
if ((rowid)) rowid = ;
if (!(rowid) && (! || ) && (rowid))
{
("<Cell ss:StyleID=\"{0}{1}\"><Data ss:Type=\"{2}\">{3}</Data></Cell>",
cellClass, (), (), row[rowid]);
}
}
("</Row>");
}
#region tail
("</Table>");
("<WorksheetOptions>");
("<PageSetup>");
("<Layout x:CenterHorizontal=\"1\" x:Orientation=\"Landscape\" />");
("<Footer x:Data=\"Page &amp;P of &amp;N\" x:Margin=\"0.5\" />");
("<PageMargins x:Top=\"0.5\" x:Right=\"0.5\" x:Left=\"0.5\" x:Bottom=\"0.8\" />");
("</PageSetup>");
("<FitToPage />");
("<Print>");
("<PrintErrors>Blank</PrintErrors>");
("<FitWidth>1</FitWidth>");
("<FitHeight>32767</FitHeight>");
("<ValidPrinterInfo />");
("<VerticalResolution>600</VerticalResolution>");
("</Print>");
("<Selected />");
("<DoNotDisplayGridlines />");
("<ProtectObjects>False</ProtectObjects>");
("<ProtectScenarios>False</ProtectScenarios>");
("</WorksheetOptions>");
("</Worksheet></Workbook>");
#endregion
();
if ( != "IE")
("Content-Disposition", ("attachment; filename=\"{0}.xls\"", ));
else ("Content-Disposition", ("attachment; filename={0}.xls", ()));
= "application/excel";
(());
();
}
}