程序集
1 using System.IO; 2 using System.Data; 3 using System.Data.OleDb; 4 using System.Data.Odbc;
导入[.xls|.xlsx]
1 public static DataTable FromExceFile(string pathName) 2 { 3 DataSet ds = new DataSet(); 4 try 5 { 6 7 FileInfo file = new FileInfo(pathName); 8 if (!file.Exists) 9 { 10 throw new Exception("Not exist!"); 11 } 12 string ext=file.Extension.ToLower(); 13 if (ext!=".xls" && ext!="xlsx") 14 { 15 throw new Exception("File Extension Must Be:.xls Or .xlsx"); 16 } 17 18 19 string xlsConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + "" 20 + @";Extended Properties='Excel 8.0;HDR={0};IMEX=1;'", "YES"); 21 22 //"HDR=Yes;" 第一行是表头，不是数据 23 //"IMEX=1;" 把混合数据当作文本读取 24 string xlsxConn= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName 25 + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; 26 27 28 OleDbConnection cnn = new OleDbConnection(ext.Equals(".xlsx")?xlsxConn:xlsConn); 29 cnn.Open(); 30 DataTable dttemp = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 31 string tableName = dttemp.Rows[0][2].ToString().Trim(); 32 33 OleDbCommand com = new OleDbCommand("SELECT * FROM ["+tableName+"]", cnn); 34 OleDbDataAdapter objAdapter = new OleDbDataAdapter(); 35 objAdapter.SelectCommand = com; 36 objAdapter.Fill(ds, "TempTable"); 37 cnn.Close(); 38 cnn.Dispose(); 39 } 40 catch (OdbcException ex) 41 { 42 43 } 44 45 DataTable dt = ds.Tables["TempTable"]; 46 return dt; 47 }
导入[.cvs]
1 public static DataTable FromCsvFile(string pathName) 2 { 3 DataSet ds = new DataSet(); 4 DataTable dt; 5 try 6 { 7 8 FileInfo file = new FileInfo(pathName); 9 if (!file.Exists) 10 { 11 throw new Exception("Not exist!"); 12 } 13 if (file.Extension.ToLower()!=".csv") 14 { 15 throw new Exception("File Extension Must Be .csv"); 16 } 17 string csvConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName 18 + ";Extended Properties='Text;HDR=YES;'"; 19 OleDbConnection cnn = new OleDbConnection(csvConn); 20 cnn.Open(); 21 OleDbCommand com = new OleDbCommand("SELECT * FROM ["+file.Name +"]", cnn); 22 OleDbDataAdapter da=new OleDbDataAdapter(); 23 da.SelectCommand = com; 24 da.Fill(ds, "TempTable"); 25 da.Dispose(); 26 cnn.Close(); 27 cnn.Dispose(); 28 29 30 } 31 catch 32 { 33 dt=new DataTable(); 34 return dt; 35 } 36 37 dt=ds.Tables["TempTable"]; 38 39 return dt; 40 41 }
导出[.xls|.cvs]
1 public static void ExportExcel() 2 { 3 List<string[]> exportDataList = new List<string[]>(); 4 int FieldCount=3; 5 string[] titleRow = new string[FieldCount]; 6 int i=0; 7 titleRow[i++]="Name"; 8 titleRow[i++]="Age"; 9 titleRow[i++]="Tel"; 10 exportDataList.Add(titleRow); 11 12 for(int k=0;k<10;k++) 13 { 14 i=0; 15 string[] row = new string[FieldCount]; 16 row[i++]="Name "+k; 17 row[i++]="Age "+k; 18 row[i++]="Tel "+k; 19 exportDataList.Add(row); 20 } 21 Export(exportDataList,"export.xls"); 22 } 23 public static void Export(List<string[]> dataList,string fileName) 24 { 25 26 string rowTmp="<td style=\"vnd.ms-excel.numberformat:@\">{0}</td>"; 27 string html = ""; 28 html += "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"; 29 html += "<head>"; 30 html += "<!--[if gte mso 9]>"; 31 html += "<xml>"; 32 html += " <x:ExcelWorkbook>"; 33 html += " <x:ExcelWorksheets>"; 34 html += " <x:ExcelWorksheet>"; 35 html += " <x:Name>Winner</x:Name>"; 36 html += " <x:WorksheetOptions>"; 37 html += " <x:Print>"; 38 html += " <x:ValidPrinterInfo />"; 39 html += " </x:Print>"; 40 html += " </x:WorksheetOptions>"; 41 html += " </x:ExcelWorksheet>"; 42 html += " </x:ExcelWorksheets>"; 43 html += "</x:ExcelWorkbook>"; 44 html += "</xml>"; 45 html += "<![endif]-->"; 46 html += "</head>"; 47 html += "<body>"; 48 html += "<table>"; 49 50 bool IsHeader=true; 51 foreach (var row in dataList) 52 { 53 string r=""; 54 string rowTemplate=rowTmp; 55 if (IsHeader) 56 { 57 IsHeader=false; 58 rowTemplate="<td><strong>{0}</strong></td>"; 59 } 60 61 foreach(var fielddata in row) 62 { 63 r+=string.Format(rowTemplate, fielddata); 64 } 65 66 html+=(string.Format("<tr>{0}</tr>", r)); 67 } 68 69 70 html += "</table>"; 71 html += "</body>"; 72 html += "</html>"; 73 74 75 HttpResponse resp=Page.Response; 76 resp.ContentEncoding = System.Text.Encoding.Default; 77 resp.AppendHeader("Content-Disposition", "attachment;filename="+fileName); 78 resp.ContentType = "application/ms-excel"; 79 resp.Clear(); 80 resp.Write(html); 81 resp.Flush(); 82 resp.End(); 83 }
Excel单元格的格式：
使用<td style="vnd.ms-excel.numberformat:@"></td> 进行设置
//1） 文本：vnd.ms-excel.numberformat:@
//2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd
//3） 数字：vnd.ms-excel.numberformat:#,##0.00
//4） 货币：vnd.ms-excel.numberformat:￥#,##0.00
//5） 百分比：vnd.ms-excel.numberformat: #0.00%
1 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 2 xmlns:x="urn:schemas-microsoft-com:office:excel" 3 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 4 xmlns:html="http://www.w3.org/TR/REC-html40"> 5 6 <x:ExcelWorkbook> 7 <WindowHeight>1000</WindowHeight> 8 <WindowWidth>1000</WindowWidth> 9 </x:ExcelWorkbook> 10 11 <ss:Styles> 12 <ss:Style ss:ID="Default" ss:Name="Normal"> 13 <Font x:Family="Swiss" ss:Size="10" ss:Bold="0"/> 14 <ss:Alignment ss:Vertical="Bottom"/> 15 </ss:Style> 16 <ss:Style ss:ID="s22"> 17 <Font x:Family="Swiss" ss:Size="12" ss:Bold="1"/> 18 <ss:NumberFormat ss:Format="0.00;[Red]0.00"/> 19 </ss:Style> 20 </ss:Styles> 21 22 23 <Worksheet ss:Name="Sheet one"> 24 25 <ss:Table> 26 <ss:Row> 27 <ss:Cell> 28 <ss:Data ss:Type="String">Total</ss:Data> 29 </ss:Cell> 30 <ss:Cell ss:StyleID="s22"> 31 <ss:Data ss:Type="Number">-45</ss:Data> 32 </ss:Cell> 33 <ss:Cell ss:StyleID="s22"> 34 <ss:Data ss:Type="Number">-99.3</ss:Data> 35 </ss:Cell> 36 37 <ss:Cell ss:Index="8" ss:Formula="=AVERAGE(RC[-6]:RC[-5])"></ss:Cell> 38 </ss:Row> 39 40 <ss:Row> 41 <ss:Cell ss:MergeAcross="1" ss:MergeDown="1"> 42 <ss:Data ss:Type="String">Monday tip</ss:Data> 43 <ss:Comment ss:Author="Author" ss:ShowAlways="1"> 44 <ss:Data><html:B><html:Font html:Face="Tahoma" html:Size="8" html:Color="000000">Author:</html:Font></html:B> 45 <html:Font html:Face="Tahoma" html:Size="8" html:Color="000000">&10;The </html:Font> 46 <html:B><html:Font html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="000000">first</html:Font></html:B> 47 <html:Font html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="000000"> day of the week.</html:Font> 48 </ss:Data> 49 </ss:Comment> 50 </ss:Cell> 51 <ss:Cell ss:Index="7" ss:HRef="http://www.microsoft.com"> 52 <ss:Data ss:Type="String">Linked Cell</ss:Data> 53 </ss:Cell> 54 55 </ss:Row> 56 </ss:Table> 57 </Worksheet> 58 59 60 61 </Workbook>
该实例使用的命名空间：
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"
