因为微软的office成本太高了，所以开发项目的时候电脑上没安装office,而是安装了wps。但开发语言用的是C#,所以直接调用微软的office组件是很方便的，但一方面慢，一方面成本高，所以从网上找到了NPOI这个开源的项目。http://npoi.codeplex.com/,引用的dll下载目录 http://npoi.codeplex.com/downloads/get/1476595
并且封装了通用的处理Excel跟DataSet,DataTable的方法。方便调用
以上是代码 （当前项目是.net 2.0 下的，如果需要.net 4.0则到NPOI官网下载相应的dll就可以了）
1usingNPOI.SS.UserModel;2usingSystem;3usingSystem.Collections.Generic;4usingSystem.Data;5usingSystem.IO;6usingSystem.Text;78namespaceMrLiu.Tools9{10publicsealedclassExcelHelper11{12#regionExcel导入13///<summary>14///Excel 转换为DataTable15///</summary>16///<param name="file">文件路径</param>17///<param name="sheetName">Sheet名称，如果只有一个sheet可以传 null</param>18///<returns></returns>19publicstaticDataTable ExcelToDataTable(stringfile,stringsheetName)20{21try22{23DataTable dt =newDataTable();24using(FileStream fs =newFileStream(file, FileMode.Open, Fileaccess.Read, FileShare.Read))25{26varworkbook =NPOI.SS.UserModel.WorkbookFactory.Create(fs);27ISheet sheet =null;28if(sheetName ==null)29{30sheet = workbook.GetSheetAt(0);31}32else33{34sheet =workbook.GetSheet(sheetName);35}36//列名37IRow rowHead =sheet.GetRow(sheet.FirstRowNum);38for(inti =0; i < rowHead.LastCellNum; i++)39{40stringfildName =rowHead.GetCell(i).StringCellValue;41dt.Columns.Add(fildName,typeof(String));42}4344//数据45for(inti = sheet.FirstRowNum +1; i <= sheet.LastRowNum; i++)46{47IRow row =sheet.GetRow(i);48DataRow dr =dt.NewRow();49for(intj = row.FirstCellNum; j < row.LastCellNum; j++)50{51varcell =row.GetCell(j);52dr[j] =GetValueTypeForICell(cell);53if(dr[j] ==null)54{55dr[j] =string.Empty;56}57}58dt.Rows.Add(dr);59}60}61returndt;62}63catch(Exception ex)64{65thrownewException(ex.Message);66}67}68///<summary>69///Excel 导入为DataTable70///</summary>71///<param name="file">文件路径</param>72///<param name="extension">后续名 XLS XLSX</param>73///<returns></returns>74publicstaticDataTable ExcelToDataTable(stringfile)75{76try77{78DataTable dt =newDataTable();79stringextension =Path.GetExtension(file);80if(extension.ToUpper() ==".XLS")81{82dt =ExcelToTableForXLS(file);83}84elseif(extension.ToUpper() ==".XLS")85{86dt =ExcelToTableForXLSX(file);87}88else89{90thrownewException("文件格式不正确");91}92returndt;93}94catch(Exception ex)95{96thrownewException(ex.Message);97}98}99///<summary>100///读取xls格式的Excel101///</summary>102///<param name="file">文件全路径</param>103///<returns>返回DaTaTable</returns>104publicstaticDataTable ExcelToTableForXLS(stringfile)105{106try107{108DataTable dt =newDataTable();109using(FileStream fs =newFileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))110{111varhssfworkbook =newNPOI.HSSF.UserModel.HSSFWorkbook(fs);112ISheet sheet = hssfworkbook.GetSheetAt(0);113114//列名115IRow rowHead =sheet.GetRow(sheet.FirstRowNum);116for(inti =0; i < rowHead.LastCellNum; i++)117{118stringfildName =rowHead.GetCell(i).StringCellValue;119dt.Columns.Add(fildName,typeof(String));120}121122//数据123for(inti = sheet.FirstRowNum +1; i <= sheet.LastRowNum; i++)124{125IRow row =sheet.GetRow(i);126DataRow dr =dt.NewRow();127for(intj = row.FirstCellNum; j < row.LastCellNum; j++)128{129NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j)asNPOI.HSSF.UserModel.HSSFCell;130dr[j] =GetValueTypeForXLS(cell);131if(dr[j] ==null)132{133
break;134}135}136dt.Rows.Add(dr);137}138}139returndt;140}141catch(Exception ex)142{143thrownewException(ex.Message);144}145}146147///<summary>148///获取单元格类型149///</summary>150///<param name="cell"></param>151///<returns></returns>152PRivatestaticobjectGetValueTypeForXLS(NPOI.HSSF.UserModel.HSSFCell cell)153{154try155{156if(cell ==null)157{158returnnull;159}160switch(cell.CellType)161{162caseCellType.Blank://BLANK:163returnnull;164caseCellType.Boolean://BOOLEAN:165returncell.BooleanCellValue;166caseCellType.Numeric://NUMERIC:167returncell.NumericCellValue;168caseCellType.String://STRING:169returncell.StringCellValue;170caseCellType.Error://ERROR:171returncell.ErrorCellValue;172caseCellType.Formula://FORMULA:173default:174return"="+cell.CellFormula;175}176}177catch(Exception ex)178{179thrownewException(ex.Message);180}181}182183///<summary>184///读取xlsx格式的Excel185///</summary>186///<param name="file">文件全路径</param>187///<returns>返回DaTaTable</returns>188publicstaticDataTable ExcelToTableForXLSX(stringfile)189{190try191{192DataTable dt =newDataTable();193using(FileStream fs =newFileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))194{195varhssfworkbook =newNPOI.XSSF.UserModel.XSSFWorkbook(fs);196ISheet sheet = hssfworkbook.GetSheetAt(0);197198//列名199IRow rowHead =sheet.GetRow(sheet.FirstRowNum);200for(inti =0; i < rowHead.LastCellNum; i++)201{202stringfildName =rowHead.GetCell(i).StringCellValue;203dt.Columns.Add(fildName,typeof(String));204}205206//数据207for(inti = sheet.FirstRowNum +1; i <= sheet.LastRowNum; i++)208{209IRow row =sheet.GetRow(i);210DataRow dr =dt.NewRow();211for(intj = row.FirstCellNum; j < row.LastCellNum; j++)212{213NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j)asNPOI.HSSF.UserModel.HSSFCell;214dr[j] =GetValueTypeForXLS(cell);215if(dr[j] ==null)216{217
break;218}219}220dt.Rows.Add(dr);221}222}223returndt;224}225catch(Exception ex)226{227thrownewException(ex.Message);228}229}230///<summary>231///获取单元格类型(xlsx)232///</summary>233///<param name="cell"></param>234///<returns></returns>235privatestaticobjectGetValueTypeForXLSX(NPOI.XSSF.UserModel.XSSFCell cell)236{237try238{239if(cell ==null)240{241returnnull;242}243switch(cell.CellType)244{245caseCellType.Blank://BLANK:246returnnull;247caseCellType.Boolean://BOOLEAN:248returncell.BooleanCellValue;249caseCellType.Numeric://NUMERIC:250returncell.NumericCellValue;251caseCellType.String://STRING:252returncell.StringCellValue;253caseCellType.Error://ERROR:254returncell.ErrorCellValue;255caseCellType.Formula://FORMULA:256default:257return"="+cell.CellFormula;258}259}260catch(Exception ex)261{262thrownewException(ex.Message);263}264}265266///<summary>267///获取单元格类型不定268///</summary>269///<param name="cell"></param>270///<returns></returns>271privatestaticobjectGetValueTypeForICell(ICell cell)272{273try274{275if(cell ==null)276{277returnnull;278}279switch(cell.CellType)280{281caseCellType.Blank://BLANK:282returnnull;283caseCellType.Boolean://BOOLEAN:284returncell.BooleanCellValue;285caseCellType.Numeric://NUMERIC:286returncell.NumericCellValue;287caseCellType.String://STRING:288returncell.StringCellValue;289caseCellType.Error://ERROR:290returncell.ErrorCellValue;291caseCellType.Formula://FORMULA:292default:293return"="+cell.CellFormula;294}295}296catch(Exception ex)297{298thrownewException(ex.Message);299}300}301302///<summary>303///Excel 转换为DataSet304///</summary>305///<param name="fileName">文件名</param>306///<returns>DataSet</returns>307publicstaticDataSet ExcelToDataSet(stringfileName)308{309try310{311if(!File.Exists(fileName))312{313thrownewException("文件不存在");314}315else316{317DataSet ds =newDataSet();318using(FileStream reader =newFileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))319{320IWorkbook book =WorkbookFactory.Create(reader);321intcnt =book.NumberOfSheets;322if(cnt <=0)323{324thrownewException("文件不是Excel文件");325}326327for(inti =0; i < cnt; i++)328{329ISheet sheet =book.GetSheetAt(i);330DataTable dt =newDataTable(sheet.SheetName);331IRow rowHead =sheet.GetRow(sheet.FirstRowNum);332for(intj = rowHead.FirstCellNum; j < rowHead.LastCellNum; j++)333{334ICell cell =rowHead.GetCell(j);335dt.Columns.Add(cell.StringCellValue);336}337for(intj = sheet.FirstRowNum +1; j <= sheet.LastRowNum; j++)338{339DataRow dr =dt.NewRow();340IRow row =sheet.GetRow(j);341for(intk = rowHead.FirstCellNum; k < rowHead.LastCellNum; k++)342{343dr[k] =row.GetCell(k).StringCellValue;344}345dt.Rows.Add(dr);346}347ds.Tables.Add(dt);348}349}350returnds;351}352}353catch(Exception ex)354{355thrownewException(ex.Message);356}357}358#endregionExcel导出359360#regionExcel导出361362///<summary>363///Excel导出364///</summary>365///<param name="dt">虚拟表</param>366///<param name="fileName">文件路径</param>367///<param name="sheetName">Sheet路径为空请传null</param>368///<returns></returns>369publicstaticboolDataTableToXLS(DataTable dt,stringfileName,stringsheetName)370{371try372{373if(dt ==null)374{375returnfalse;376}377if(String.IsNullOrEmpty(sheetName))378{379sheetName =Path.GetFileName(fileName);380}381varbook =newNPOI.HSSF.UserModel.HSSFWorkbook();382book.CreateSheet();383varsheet =book.CreateSheet(sheetName);384385IRow rowHead = sheet.CreateRow(0);386for(inti =0; i < dt.Columns.Count; i++)387{388ICell cell =rowHead.CreateCell(i);389cell.SetCellValue(dt.Columns[i].ColumnName);390}391for(inti =0; i < dt.Rows.Count; i++)392{393IRow row = sheet.CreateRow(i +1);394for(intj =0; j < dt.Columns.Count; j++)395{396ICell cell =row.CreateCell(j);397cell.SetCellValue(dt.Rows[i][j].ToString());398}399}400401using(FileStream fsWriter =newFileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Write))402{403book.Write(fsWriter);404returntrue;405}406}407catch(Exception ex)408{409thrownewException(ex.Message);410}411}412413414///<summary>415///DataSet 导出 到Excel416///</summary>417///<param name="ds">DataSet 表名默认为sheet名</param>418///<param name="fileName">文件路径</param>419publicstaticboolDataSetToExcel(DataSet ds,stringfileName)420{421try422{423String extension =Path.GetExtension(fileName).ToUpper();424IWorkbook book =null;425if(extension ==".XLS")426{427book =DataSetToHSSFWordbook(ds);428}429elseif(extension ==".XLSX")430{431book =DataSetToXSSFWorkbook(ds);432}433else434{435thrownewException("导入格式必须为xls或者xlsx");436}437438using(FileStream fsWriter =newFileStream(fileName, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite))439{440book.Write(fsWriter);441returntrue;442}443}444catch(Exception ex)445{446thrownewException(ex.Message);447}448}449///<summary>450///DataSet 转换为 XSSFWorkbook 07451///</summary>452///<param name="ds"></param>453///<returns></returns>454privatestaticNPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)455{456try457{458varbook =newNPOI.XSSF.UserModel.XSSFWorkbook();459foreach(DataTable dtinds.Tables)460{461ISheet sheet =book.CreateSheet(dt.TableName);462IRow rowHead = sheet.CreateRow(0);463ICellStyle style =book.CreateCellStyle();464style.BorderBottom =BorderStyle.Thin;465style.BorderTop =BorderStyle.Thin;466style.BorderLeft =BorderStyle.Thin;467style.BorderRight =BorderStyle.Thin;468IFont font =book.CreateFont();469font.FontHeightInPoints =12;470font.IsBold =true;471style.SetFont(font);472for(inti =0; i < dt.Columns.Count; i++)473{474ICell cell =rowHead.CreateCell(i);475cell.CellStyle =style;476cell.SetCellValue(dt.Columns[i].ColumnName);477}478font.IsBold =false;479style.SetFont(font);480for(inti =0; i < dt.Rows.Count; i++)481{482IRow row = sheet.CreateRow(i +1);483DataRow dr =dt.Rows[i];484for(intj =0; j < dt.Columns.Count; j++)485{486ICell cell =row.CreateCell(j);487cell.CellStyle =style;488cell.SetCellValue(dr[j].ToString());489}490}491}492returnbook;493}494catch(Exception ex)495{496thrownewException(ex.Message);497}498}499500///<summary>501///DataSet 转换为 HSSFWorkbook 03502///</summary>503///<param name="ds"></param>504///<returns></returns>505privatestaticNPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)506{507try508{509varbook =newNPOI.HSSF.UserModel.HSSFWorkbook();510vardsi =NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();511dsi.Company = "xx软件股份有限公司";512varsi =NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();513si.Subject ="xx系统自动导出";514book.DocumentSummaryInformation =dsi;515book.SummaryInformation =si;516517foreach(DataTable dtinds.Tables)518{519ISheet sheet =book.CreateSheet(dt.TableName);520IRow rowHead = sheet.CreateRow(0);521ICellStyle style =book.CreateCellStyle();522style.BorderBottom =BorderStyle.Thin;523style.BorderTop =BorderStyle.Thin;524style.BorderLeft =BorderStyle.Thin;525style.BorderRight =BorderStyle.Thin;526IFont font =book.CreateFont();527font.FontHeightInPoints =12;528font.IsBold =true;529style.SetFont(font);530for(inti =0; i < dt.Columns.Count; i++)531{532ICell cell =rowHead.CreateCell(i);533cell.CellStyle =style;534cell.SetCellValue(dt.Columns[i].ColumnName);535}536font.IsBold =false;537style.SetFont(font);538for(inti =0; i < dt.Rows.Count; i++)539{540IRow row = sheet.CreateRow(i +1);541DataRow dr =dt.Rows[i];542for(intj =0; j < dt.Columns.Count; j++)543{544ICell cell =row.CreateCell(j);545cell.CellStyle =style;546cell.SetCellValue(dr[j].ToString());547}548}549}550returnbook;551}552catch(Exception ex)553{554thrownewException(ex.Message);555}556}557558#endregion559}560}