首页 > 代码库 > C#关于导出excel的方法
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 }
C#关于导出excel的方法
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。