try {
string OutFileName = "北京市国控企业污染源废气在线比对监测数据审核表" + DateTime.Now.ToString("yyyy-MM-dd");
string templateName = "online_gas_template.xml";
if (OutFileName.Contains("xls"))
{
OutFileName = OutFileName.Substring(0, OutFileName.LastIndexOf('.'));
}
int mergeColumnCount = 10;
string strSql = string.Empty;
DataTable dsData = dalReport.GetExportData(this.txtEnterName.Text, PollutionName.Text, ddl_Year.SelectedValue, ddl_Quarter.SelectedValue, ddl_Area.Items[ddl_Area.SelectedIndex].Text, ddl_Result.SelectedValue, radio_Used.SelectedValue); if (null == dsData) return;
int intColCount = dsData.Columns.Count;
int intRowCount = dsData.Rows.Count;
DataColumnCollection colName = dsData.Columns;
//构造EXCLE字符串 string newFilePath = this.Server.MapPath("..") + "\\temp\\" + OutFileName + ".xls"; System.IO.StreamWriter sw = new System.IO.StreamWriter(newFilePath);
string filePath = this.Server.MapPath("..") + "\\ExcelTemplet\\" + templateName;
System.IO.FileStream tempFile = new System.IO.FileStream(filePath, System.IO.FileMode.Open);//读取模板文件
System.IO.StreamReader sr = new System.IO.StreamReader(tempFile);
string tempStr = sr.ReadToEnd();
sr.Close();
//内容开始 StringBuilder sbContent = new StringBuilder();
//申明两个数组,初始为0
int[] mergeDownArr = new int[mergeColumnCount];//用来保存要向下合并的行数,运行到下一行时递减
int[] mergeDownTotalArr = new int[mergeColumnCount];//记录一下合并行总数
for (int rI = 0; rI < intRowCount; rI++)//循环输出行 { sbContent.Append("<Row>"); for (int mI = 0; mI < mergeColumnCount; mI++)//循环有合并情况的列 { if (mergeDownArr[mI] <= 0) { for (int n = rI + 1; n < intRowCount; n++) { if (dsData.Rows[n][mI].ToString() == dsData.Rows[rI][mI].ToString()) { if (mI != 1)//如果不是企业名称列,其它列是否合并要根据企业名称是否相等来判断 { if (dsData.Rows[n][1].ToString() == dsData.Rows[rI][1].ToString()) { mergeDownArr[mI]++; } } else//如果是企业名称列,则直接加 { mergeDownArr[mI]++; } } else { break; } } mergeDownTotalArr[mI] = mergeDownArr[mI]; } else { mergeDownArr[mI]--; } } //要对数据的前mergeColumnCount个列进行合并相关处理 for (int cI = 0; cI < mergeColumnCount; cI++) { int cellIndex = cI + 1; if (mergeDownArr[cI] == mergeDownTotalArr[cI] && mergeDownTotalArr[cI] > 0)//要输出的具有合并标志的第一行 { sbContent.Append("<Cell ss:Index=\"" + cellIndex + "\" ss:MergeDown=\"" + mergeDownTotalArr[cI] + "\" ss:StyleID=\"s21\"><Data ss:Type=\"String\">" + dsData.Rows[rI][cI].ToString().Replace("<", "<") + "</Data></Cell>"); } else if (mergeDownTotalArr[cI] == 0) {
sbContent.Append("<Cell ss:Index=\"" + cellIndex + "\" ss:StyleID=\"s21\"><Data ss:Type=\"String\">" + dsData.Rows[rI][cI].ToString().Replace("<", "<") + "</Data></Cell>"); } } //处理剩下不需要合并的列 string ssIndex = string.Empty; if (mergeColumnCount > 0) { ssIndex = "ss:Index=\"" + (mergeColumnCount + 1).ToString() + "\""; } for (int remainC = mergeColumnCount; remainC < intColCount; remainC++)//循环输出剩下不需要合并的列 { if (remainC == mergeColumnCount) { sbContent.Append("<Cell ss:StyleID=\"s21\" " + ssIndex + " ><Data ss:Type=\"String\">" + dsData.Rows[rI][remainC].ToString().Replace("<", "<") + "</Data></Cell>"); } else { string txt = "s21"; if (remainC == 16) { if (dsData.Rows[rI][remainC].ToString().Replace("<", "<") == "否") txt = "s210"; } sbContent.Append("<Cell ss:StyleID=\"" + txt + "\"><Data ss:Type=\"String\">" + dsData.Rows[rI][remainC].ToString().Replace("<", "<") + "</Data></Cell>"); }
} sbContent.Append("</Row>"); } tempStr = tempStr.Replace("{Content}", sbContent.ToString()); sw.Write(tempStr); sw.Close(); ExecJs("window.open('../temp/" + OutFileName + ".xls');", Page); } catch (System.Exception E) { }
模板文件内容
<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><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=""> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Version>14.00</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <AllowPNG/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>10005</WindowHeight> <WindowWidth>10005</WindowWidth> <WindowTopX>120</WindowTopX> <WindowTopY>135</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Center"/> <Borders/> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s62" ss:Name="常规 2"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="m45960948" ss:Parent="s62"> <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s63"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> </Style> <Style ss:ID="s65"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="16" ss:Color="#000000" ss:Bold="1"/> </Style> <Style ss:ID="s68" ss:Parent="s62"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000" ss:Bold="1"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s69" ss:Parent="s62"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Tahoma" x:CharSet="134" x:Family="Swiss" ss:Size="9" ss:Color="#000000" ss:Bold="1"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s70" ss:Parent="s62"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000" ss:Bold="1"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s71" ss:Parent="s62"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Tahoma" x:CharSet="134" x:Family="Swiss" ss:Size="9" ss:Color="#000000" ss:Bold="1"/> <Interior/> <NumberFormat/> <Protection/> </Style> <!--通用样式s21,程序里写入行数据时使用此样式,不可少--> <Style ss:ID="s21"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> </Style> </Styles> <Worksheet ss:Name="污水处理厂在线比对监测数据审核表"> <Table ss:ExpandedColumnCount="18" x:FullColumns="1" x:FullRows="1" ss:StyleID="s63" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5"> <Column ss:Index="6" ss:StyleID="s63" ss:AutoFitWidth="0" ss:Width="69.75"/> <Row ss:AutoFitHeight="0" ss:Height="42.5625"> <Cell ss:MergeAcross="17" ss:StyleID="s65"><Data ss:Type="String">北京市国控企业污染源污水处理厂在线比对监测数据审核表</Data></Cell> </Row> <Row ss:AutoFitHeight="0" ss:Height="24"> <Cell ss:StyleID="s68"><Data ss:Type="String">区县名称</Data></Cell> <Cell ss:StyleID="s68"><Data ss:Type="String">企业名称</Data></Cell> <Cell ss:StyleID="s69"><Data ss:Type="String">监测点名称</Data></Cell> <Cell ss:StyleID="s68"><Data ss:Type="String">自动监测系统安装位置</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">监测原理</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">自动监测设备型号</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">自动监测设备生产厂家</Data></Cell> <Cell ss:StyleID="s70"> <Data ss:Type="String">比对监测日期</Data> </Cell> <Cell ss:StyleID="s71"> <Data ss:Type="String">监测因子</Data> </Cell> <Cell ss:StyleID="s71"> <Data ss:Type="String">数据类型</Data> </Cell> <Cell ss:StyleID="s71"><Data ss:Type="String">手工监测数据</Data></Cell> <Cell ss:StyleID="s71"><Data ss:Type="String">自动监测数据</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">单位</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">比对结果</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">评判标准</Data></Cell> <Cell ss:StyleID="s70"> <Data ss:Type="String">是否合格</Data> </Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">监测项目是否合格</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="String">监测点位全项目是否合格</Data></Cell> </Row> {Content} <Row ss:AutoFitHeight="0"> <Cell ss:MergeAcross="17" ss:StyleID="m45960948"><Data ss:Type="String">签发: 审核: 录入: 第 页/共 页 </Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <PageSetup> <Header x:Margin="0.3"/> <Footer x:Margin="0.3"/> <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> </PageSetup> <Unsynced/> <Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <HorizontalResolution>200</HorizontalResolution> <VerticalResolution>200</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>2</ActiveRow> <RangeSelection>R3C1:R3C18</RangeSelection> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet></Workbook>