NPOIHelper.cs(NPOI2.1.1)

1usingSystem;2usingSystem.Collections.Generic;3usingSystem.Linq;4usingSystem.Text;5usingSystem.Data;6usingSystem.IO;7usingNPOI.XSSF.UserModel;8usingNPOI.SS.UserModel;910namespaceNetLib11{12publicstaticclassNPOIHelper13{14publicstaticvoidExportToFile(DataSet dataSet,stringfileFullPath)15{16List<DataTable> dts =newList<DataTable>();17foreach(DataTable dtindataSet.Tables) dts.Add(dt);18ExportToFile(dts, fileFullPath);19}20publicstaticvoidExportToFile(DataTable dataTable,stringfileFullPath)21{22List<DataTable> dts =newList<DataTable>();23dts.Add(dataTable);24ExportToFile(dts, fileFullPath);25}26publicstaticvoidExportToFile(IEnumerable<DataTable> dataTables,stringfileFullPath)27{28IWorkbook workbook =newXSSFWorkbook();29inti =0;30foreach(DataTable dtindataTables)31{32stringsheetName =string.IsNullOrEmpty(dt.TableName)33?"Sheet"+ (++i).ToString()34: dt.TableName;35ISheet sheet =workbook.CreateSheet(sheetName);3637IRow headerRow = sheet.CreateRow(0);38for(intj =0; j < dt.Columns.Count; j++)39{40stringcolumnName =string.IsNullOrEmpty(dt.Columns[j].ColumnName)41?"Column"+j.ToString()42: dt.Columns[j].ColumnName;43headerRow.CreateCell(j).SetCellValue(columnName);44}4546for(inta =0; a < dt.Rows.Count; a++)47{48DataRow dr =dt.Rows[a];49IRow row = sheet.CreateRow(a +1);50for(intb =0; b < dt.Columns.Count; b++)51{52row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() :string.Empty);53}54}55}5657using(FileStream fs =File.Create(fileFullPath))58{59workbook.Write(fs);60}61}6263publicstaticList<DataTable> GetDataTablesFrom(stringxlsxFile)64{65if(!File.Exists(xlsxFile))66thrownewFileNotFoundException("文件不存在");6768List<DataTable> result =newList<DataTable>();69Stream stream =newMemoryStream(File.ReadAllBytes(xlsxFile));70IWorkbook workbook =newXSSFWorkbook(stream);71for(inti =0; i < workbook.NumberOfSheets; i++)72{73DataTable dt =newDataTable();74ISheet sheet =workbook.GetSheetAt(i);75IRow headerRow = sheet.GetRow(0);7677intcellCount =headerRow.LastCellNum;78for(intj = headerRow.FirstCellNum; j < cellCount; j++)79{80DataColumn column =newDataColumn(headerRow.GetCell(j).StringCellValue);81dt.Columns.Add(column);82}8384introwCount =sheet.LastRowNum;85for(inta = (sheet.FirstRowNum +1); a < rowCount; a++)86{87IRow row =sheet.GetRow(a);88if(row ==null)continue;8990DataRow dr =dt.NewRow();91for(intb = row.FirstCellNum; b < cellCount; b++)92{93if(row.GetCell(b) ==null)continue;94dr[b] =row.GetCell(b).ToString();95}9697dt.Rows.Add(dr);98}99result.Add(dt);100}101stream.Close();102103returnresult;104}105}106}

NPOI 项目： http://npoi.codeplex.com/

本地下载：http://files.cnblogs.com/bruceleeliya/NPOI2.1.1.zip