正文

C#关于导出excel的方法

IP云
IP云V管理员/3 阅读
08/12
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一说到导出excel可能很多人都会觉得说直接利用ｎｐｏｉ导入导出ｅｘｃｅｌ格式的文件，那样非常方便，但是可能有些时候有的浏览器不支持，那么该怎么办呢，现在介绍一种纯Ｃ#的导出ｅｘｃｅｌ的方法，代码如下：

public void excel_output1(DataTable dt)
{
Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8 ";
Response.AddHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", System.Text.Encoding.UTF8));
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter(); sw.WriteLine("<?xml version=\"1.0\"?>");
sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sw.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
sw.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
sw.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sw.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
sw.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
sw.WriteLine(" <Created>1996-12-17T01:32:42Z</Created>");
sw.WriteLine(" <LastSaved>2011-05-29T05:59:50Z</LastSaved>");
sw.WriteLine(" <Version>11.6360</Version>");
sw.WriteLine(" </DocumentProperties>");
sw.WriteLine(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">");
sw.WriteLine(" <RemovePersonalInformation/>");
sw.WriteLine(" </OfficeDocumentSettings>");
sw.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine(" <WindowHeight>4530</WindowHeight>");
sw.WriteLine(" <WindowWidth>8505</WindowWidth>");
sw.WriteLine(" <WindowTopX>480</WindowTopX>");
sw.WriteLine(" <WindowTopY>120</WindowTopY>");
sw.WriteLine(" <AcceptLabelsInFormulas/>");
sw.WriteLine(" <ProtectStructure>False</ProtectStructure>");
sw.WriteLine(" <ProtectWindows>False</ProtectWindows>");
sw.WriteLine(" </ExcelWorkbook>");
sw.WriteLine(" <Styles>");
sw.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
sw.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>");
sw.WriteLine(" <Borders/>");
sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
sw.WriteLine(" <Interior/>");
sw.WriteLine(" <NumberFormat/>");
sw.WriteLine(" <Protection/>");
sw.WriteLine(" </Style>");
sw.WriteLine(" <Style ss:ID=\"s28\">");
sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>");
sw.WriteLine(" <Borders>");
sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" </Borders>");
sw.WriteLine(" </Style>");
sw.WriteLine(" <Style ss:ID=\"s29\">");
sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
sw.WriteLine(" <Borders>");
sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" </Borders>");
sw.WriteLine(" </Style>");
sw.WriteLine(" <Style ss:ID=\"s31\">");
sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
sw.WriteLine(" <Borders>");
sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" </Borders>");
sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"24\" ss:Bold=\"1\"/>");
sw.WriteLine(" </Style>");
sw.WriteLine(" <Style ss:ID=\"s32\">");
sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>");
sw.WriteLine(" <Borders>");
sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" </Borders>");
sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>");
sw.WriteLine(" </Style>");
sw.WriteLine(" <Style ss:ID=\"s33\">");
sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
sw.WriteLine(" <Borders>");
sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
sw.WriteLine(" </Borders>");
sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>");
sw.WriteLine(" </Style>");
sw.WriteLine(" </Styles>");
sw.WriteLine(" <Worksheet ss:Name=\"Sheet1\">");
//sw.WriteLine(" <Table ss:ExpandedColumnCount=\"9\" ss:ExpandedRowCount=\"9\" x:FullColumns=\"1\""); string dt_str = "<Table ss:ExpandedColumnCount=\"13\" ss:ExpandedRowCount=\"";
int dt_rCount = dt.Rows.Count + ;
dt_str += dt_rCount.ToString() + "\" x:FullColumns=\"1\"";
sw.WriteLine(dt_str); sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">"); sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"93.75\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"111.75\" ss:Span=\"1\"/>");
sw.WriteLine(" <Column ss:Index=\"4\" ss:AutoFitWidth=\"0\" ss:Width=\"102\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"135.75\" ss:Span=\"3\"/>");
sw.WriteLine(" <Column ss:Index=\"9\" ss:AutoFitWidth=\"0\" ss:Width=\"183.75\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"63.75\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"183.75\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"63.75\"/>");
sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"163.75\"/>");
sw.WriteLine(" <Row ss:Index=\"2\">");
sw.WriteLine(" <Cell ss:MergeAcross=\"12\" ss:MergeDown=\"2\" ss:StyleID=\"s31\"><Data");
sw.WriteLine(" ss:Type=\"String\">回访详单</Data></Cell>");
sw.WriteLine(" </Row>");
sw.WriteLine(" <Row ss:Index=\"5\">");
sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">营业区</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">任务总数</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">明确表示不使用</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">迁移无线</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">改网通</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">改电信</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">联系不上的1个月以上无上网记录</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">联系不上有上网记录</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">对网络不满意</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">对服务不满意</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">考虑中</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">已续费</Data></Cell>");
sw.WriteLine(" </Row>"); int cs_i = ;
DataRow[] dt_myRow = dt.Select(""); foreach (DataRow dt_row in dt_myRow)
{
sw.WriteLine(" <Row>"); sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[].ToString() + "</Data></Cell>");
//sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[12].ToString() + "</Data></Cell>");
sw.WriteLine(" </Row>");
cs_i++;
} //sw.WriteLine(" <Row>");
//sw.WriteLine(" <Cell ss:MergeAcross=\"5\" ss:StyleID=\"s33\"><Data ss:Type=\"String\">合计</Data></Cell>");
//sw.WriteLine(" <Cell ss:StyleID=\"s32\" ss:Formula=\"=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),6-ROW(),0,ROW()-6,1))\"><Data ss:Type=\"Number\">1</Data></Cell>");
//sw.WriteLine(" <Cell ss:MergeAcross=\"1\" ss:StyleID=\"s32\" ><Data ss:Type=\"Number\"></Data></Cell>");
//sw.WriteLine(" </Row>");
sw.WriteLine(" </Table>");
sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine(" <Print>");
sw.WriteLine(" <ValidPrinterInfo/>");
sw.WriteLine(" <PaperSizeIndex>9</PaperSizeIndex>");
sw.WriteLine(" <HorizontalResolution>600</HorizontalResolution>");
sw.WriteLine(" <VerticalResolution>600</VerticalResolution>");
sw.WriteLine(" </Print>");
sw.WriteLine(" <Selected/>");
sw.WriteLine(" <Panes>");
sw.WriteLine(" <Pane>");
sw.WriteLine(" <Number>3</Number>");
sw.WriteLine(" <ActiveRow>8</ActiveRow>");
sw.WriteLine(" <ActiveCol>12</ActiveCol>");
sw.WriteLine(" </Pane>");
sw.WriteLine(" </Panes>");
sw.WriteLine(" <ProtectObjects>False</ProtectObjects>");
sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
sw.WriteLine(" </WorksheetOptions>");
sw.WriteLine(" </Worksheet>");
sw.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
sw.WriteLine(" <Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine(" <ProtectObjects>False</ProtectObjects>");
sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
sw.WriteLine(" </WorksheetOptions>");
sw.WriteLine(" </Worksheet>");
sw.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
sw.WriteLine(" <Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine(" <ProtectObjects>False</ProtectObjects>");
sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
sw.WriteLine(" </WorksheetOptions>");
sw.WriteLine(" </Worksheet>");
sw.WriteLine("</Workbook>"); Response.Write(sw);
Response.End(); }