C#关于导出excel的方法
一说到导出excel可能很多人都会觉得说直接利用ｎｐｏｉ 导入导出ｅｘｃｅｌ格式的文件，那样非常方便，但是可能有些时候有的浏览器不支持，那么该怎么办呢，现在介绍一种纯Ｃ#的导出ｅｘｃｅｌ的方法，代码如下：
1 public void excel_output1(DataTable dt) 2 { 3 Response.Clear(); 4 5 Response.Buffer = true; 6 7 Response.Charset = "utf-8 "; 8 Response.AddHeader("Content-Disposition", "attachment;filename=" + 9 HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", System.Text.Encoding.UTF8)); 10 Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); 11 Response.ContentType = "application/ms-excel"; 12 this.EnableViewState = false; 13 System.IO.StringWriter sw = new System.IO.StringWriter(); 14 15 sw.WriteLine("<?xml version=\"1.0\"?>"); 16 sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>"); 17 sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); 18 sw.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\""); 19 sw.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); 20 sw.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); 21 sw.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); 22 sw.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"); 23 sw.WriteLine(" <Created>1996-12-17T01:32:42Z</Created>"); 24 sw.WriteLine(" <LastSaved>2011-05-29T05:59:50Z</LastSaved>"); 25 sw.WriteLine(" <Version>11.6360</Version>"); 26 sw.WriteLine(" </DocumentProperties>"); 27 sw.WriteLine(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">"); 28 sw.WriteLine(" <RemovePersonalInformation/>"); 29 sw.WriteLine(" </OfficeDocumentSettings>"); 30 sw.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 31 sw.WriteLine(" <WindowHeight>4530</WindowHeight>"); 32 sw.WriteLine(" <WindowWidth>8505</WindowWidth>"); 33 sw.WriteLine(" <WindowTopX>480</WindowTopX>"); 34 sw.WriteLine(" <WindowTopY>120</WindowTopY>"); 35 sw.WriteLine(" <AcceptLabelsInFormulas/>"); 36 sw.WriteLine(" <ProtectStructure>False</ProtectStructure>"); 37 sw.WriteLine(" <ProtectWindows>False</ProtectWindows>"); 38 sw.WriteLine(" </ExcelWorkbook>"); 39 sw.WriteLine(" <Styles>"); 40 sw.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">"); 41 sw.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>"); 42 sw.WriteLine(" <Borders/>"); 43 sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>"); 44 sw.WriteLine(" <Interior/>"); 45 sw.WriteLine(" <NumberFormat/>"); 46 sw.WriteLine(" <Protection/>"); 47 sw.WriteLine(" </Style>"); 48 sw.WriteLine(" <Style ss:ID=\"s28\">"); 49 sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>"); 50 sw.WriteLine(" <Borders>"); 51 sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 52 sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 53 sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 54 sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 55 sw.WriteLine(" </Borders>"); 56 sw.WriteLine(" </Style>"); 57 sw.WriteLine(" <Style ss:ID=\"s29\">"); 58 sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>"); 59 sw.WriteLine(" <Borders>"); 60 sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 61 sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 62 sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 63 sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 64 sw.WriteLine(" </Borders>"); 65 sw.WriteLine(" </Style>"); 66 sw.WriteLine(" <Style ss:ID=\"s31\">"); 67 sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>"); 68 sw.WriteLine(" <Borders>"); 69 sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 70 sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 71 sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 72 sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 73 sw.WriteLine(" </Borders>"); 74 sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"24\" ss:Bold=\"1\"/>"); 75 sw.WriteLine(" </Style>"); 76 sw.WriteLine(" <Style ss:ID=\"s32\">"); 77 sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>"); 78 sw.WriteLine(" <Borders>"); 79 sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 80 sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 81 sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 82 sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 83 sw.WriteLine(" </Borders>"); 84 sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>"); 85 sw.WriteLine(" </Style>"); 86 sw.WriteLine(" <Style ss:ID=\"s33\">"); 87 sw.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>"); 88 sw.WriteLine(" <Borders>"); 89 sw.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 90 sw.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 91 sw.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 92 sw.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>"); 93 sw.WriteLine(" </Borders>"); 94 sw.WriteLine(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>"); 95 sw.WriteLine(" </Style>"); 96 sw.WriteLine(" </Styles>"); 97 sw.WriteLine(" <Worksheet ss:Name=\"Sheet1\">"); 98 //sw.WriteLine(" <Table ss:ExpandedColumnCount=\"9\" ss:ExpandedRowCount=\"9\" x:FullColumns=\"1\""); 99 100 string dt_str = "<Table ss:ExpandedColumnCount=\"13\" ss:ExpandedRowCount=\""; 101 int dt_rCount = dt.Rows.Count + 5; 102 dt_str += dt_rCount.ToString() + "\" x:FullColumns=\"1\""; 103 sw.WriteLine(dt_str); 104 105 106 sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">"); 107 108 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"93.75\"/>"); 109 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"111.75\" ss:Span=\"1\"/>"); 110 sw.WriteLine(" <Column ss:Index=\"4\" ss:AutoFitWidth=\"0\" ss:Width=\"102\"/>"); 111 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"135.75\" ss:Span=\"3\"/>"); 112 sw.WriteLine(" <Column ss:Index=\"9\" ss:AutoFitWidth=\"0\" ss:Width=\"183.75\"/>"); 113 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"63.75\"/>"); 114 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"183.75\"/>"); 115 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"63.75\"/>"); 116 sw.WriteLine(" <Column ss:AutoFitWidth=\"0\" ss:Width=\"163.75\"/>"); 117 sw.WriteLine(" <Row ss:Index=\"2\">"); 118 sw.WriteLine(" <Cell ss:MergeAcross=\"12\" ss:MergeDown=\"2\" ss:StyleID=\"s31\"><Data"); 119 sw.WriteLine(" ss:Type=\"String\">回访详单</Data></Cell>"); 120 sw.WriteLine(" </Row>"); 121 sw.WriteLine(" <Row ss:Index=\"5\">"); 122 sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">营业区</Data></Cell>"); 123 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">任务总数</Data></Cell>"); 124 sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">明确表示不使用</Data></Cell>"); 125 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">迁移无线</Data></Cell>"); 126 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">改网通</Data></Cell>"); 127 sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">改电信</Data></Cell>"); 128 sw.WriteLine(" <Cell ss:StyleID=\"s32\"><Data ss:Type=\"String\">联系不上的1个月以上无上网记录</Data></Cell>"); 129 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">联系不上有上网记录</Data></Cell>"); 130 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">对网络不满意</Data></Cell>"); 131 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">对服务不满意</Data></Cell>"); 132 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">考虑中</Data></Cell>"); 133 sw.WriteLine(" <Cell ss:StyleID=\"s33\"><Data ss:Type=\"String\">已续费</Data></Cell>"); 134 sw.WriteLine(" </Row>"); 135 136 int cs_i = 1; 137 DataRow[] dt_myRow = dt.Select(""); 138 139 foreach (DataRow dt_row in dt_myRow) 140 { 141 sw.WriteLine(" <Row>"); 142 143 sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[0].ToString() + "</Data></Cell>"); 144 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[1].ToString() + "</Data></Cell>"); 145 sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[2].ToString() + "</Data></Cell>"); 146 sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[2].ToString() + "</Data></Cell>"); 147 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[4].ToString() + "</Data></Cell>"); 148 sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[5].ToString() + "</Data></Cell>"); 149 sw.WriteLine(" <Cell ss:StyleID=\"s29\"><Data ss:Type=\"String\">" + dt_row[6].ToString() + "</Data></Cell>"); 150 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[7].ToString() + "</Data></Cell>"); 151 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[8].ToString() + "</Data></Cell>"); 152 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[9].ToString() + "</Data></Cell>"); 153 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[10].ToString() + "</Data></Cell>"); 154 sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[11].ToString() + "</Data></Cell>"); 155 //sw.WriteLine(" <Cell ss:StyleID=\"s28\"><Data ss:Type=\"String\">" + dt_row[12].ToString() + "</Data></Cell>"); 156 sw.WriteLine(" </Row>"); 157 cs_i++; 158 } 159 160 161 162 //sw.WriteLine(" <Row>"); 163 //sw.WriteLine(" <Cell ss:MergeAcross=\"5\" ss:StyleID=\"s33\"><Data ss:Type=\"String\">合计</Data></Cell>"); 164 //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>"); 165 //sw.WriteLine(" <Cell ss:MergeAcross=\"1\" ss:StyleID=\"s32\" ><Data ss:Type=\"Number\"></Data></Cell>"); 166 //sw.WriteLine(" </Row>"); 167 sw.WriteLine(" </Table>"); 168 sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 169 sw.WriteLine(" <Print>"); 170 sw.WriteLine(" <ValidPrinterInfo/>"); 171 sw.WriteLine(" <PaperSizeIndex>9</PaperSizeIndex>"); 172 sw.WriteLine(" <HorizontalResolution>600</HorizontalResolution>"); 173 sw.WriteLine(" <VerticalResolution>600</VerticalResolution>"); 174 sw.WriteLine(" </Print>"); 175 sw.WriteLine(" <Selected/>"); 176 sw.WriteLine(" <Panes>"); 177 sw.WriteLine(" <Pane>"); 178 sw.WriteLine(" <Number>3</Number>"); 179 sw.WriteLine(" <ActiveRow>8</ActiveRow>"); 180 sw.WriteLine(" <ActiveCol>12</ActiveCol>"); 181 sw.WriteLine(" </Pane>"); 182 sw.WriteLine(" </Panes>"); 183 sw.WriteLine(" <ProtectObjects>False</ProtectObjects>"); 184 sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>"); 185 sw.WriteLine(" </WorksheetOptions>"); 186 sw.WriteLine(" </Worksheet>"); 187 sw.WriteLine(" <Worksheet ss:Name=\"Sheet2\">"); 188 sw.WriteLine(" <Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\""); 189 sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>"); 190 sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 191 sw.WriteLine(" <ProtectObjects>False</ProtectObjects>"); 192 sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>"); 193 sw.WriteLine(" </WorksheetOptions>"); 194 sw.WriteLine(" </Worksheet>"); 195 sw.WriteLine(" <Worksheet ss:Name=\"Sheet3\">"); 196 sw.WriteLine(" <Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\""); 197 sw.WriteLine(" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>"); 198 sw.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 199 sw.WriteLine(" <ProtectObjects>False</ProtectObjects>"); 200 sw.WriteLine(" <ProtectScenarios>False</ProtectScenarios>"); 201 sw.WriteLine(" </WorksheetOptions>"); 202 sw.WriteLine(" </Worksheet>"); 203 sw.WriteLine("</Workbook>"); 204 205 Response.Write(sw); 206 Response.End(); 207 208 }
