public ISheet GetSheet(string name) { foreach (XSSFSheet sheet in this.sheets) { if (name.Equals(sheet.SheetName, StringComparison.InvariantCultureIgnoreCase)) { return sheet; } } return null; }
5.WorkbookFactory类:
public class PropertySetFactory { public static PropertySet Create(DirectoryEntry dir, string name); public static PropertySet Create(Stream stream); public static SummaryInformation CreateSummaryInformation(); public static DocumentSummaryInformation CreateDocumentSummaryInformation(); }
6.DocumentSummaryInformation:
[Serializable] public class DocumentSummaryInformation : SpecialPropertySet { // Fields public const string DEFAULT_STREAM_NAME = "\x0005DocumentSummaryInformation"; // Methods public DocumentSummaryInformation(PropertySet ps); private void EnsureSection2(); public void RemoveByteCount(); public void RemoveCategory(); public void RemoveCompany(); public void RemoveCustomProperties(); public void RemoveDocparts(); public void RemoveHeadingPair(); public void RemoveHiddenCount(); public void RemoveLineCount(); public void RemoveLinksDirty(); public void RemoveManager(); public void RemoveMMClipCount(); public void RemoveNoteCount(); public void RemoveParCount(); public void RemovePresentationFormat(); public void RemoveScale(); public void RemoveSlideCount(); // Properties public int ByteCount { get; set; } public string Category { get; set; } public string Company { get; set; } public CustomProperties CustomProperties { get; set; } public byte[] Docparts { get; set; } public byte[] HeadingPair { get; set; } public int HiddenCount { get; set; } public int LineCount { get; set; } public bool LinksDirty { get; set; } public string Manager { get; set; } public int MMClipCount { get; set; } public int NoteCount { get; set; } public int ParCount { get; set; } public string PresentationFormat { get; set; } public override PropertyIDMap PropertySetIDMap { get; } public bool Scale { get; set; } public int SlideCount { get; set; } }
具体方法:
private void EnsureSection2() { if (this.SectionCount < 2) { MutableSection section = new MutableSection(); section.SetFormatID(SectionIDMap.DOCUMENT_SUMMARY_INFORMATION_ID2); this.AddSection(section); } }
以上只是对部分的类和方法做了简单的说明,需要了解更多的内容,可以进入官网下载源码,或者使用软件查看DLL文件。
三.NPOI操作实例:
1.枚举(Excel单元格数据类型):
/// <summary> /// 枚举(Excel单元格数据类型) /// </summary> public enum NpoiDataType { /// <summary> /// 字符串类型-值为1 /// </summary> String, /// <summary> /// 布尔类型-值为2 /// </summary> Bool, /// <summary> /// 时间类型-值为3 /// </summary> Datetime, /// <summary> /// 数字类型-值为4 /// </summary> Numeric, /// <summary> /// 复杂文本类型-值为5 /// </summary> Richtext, /// <summary> /// 空白 /// </summary> Blank, /// <summary> /// 错误 /// </summary> Error }
2. 将DataTable数据导入到excel中:
/// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param>要导入的数据</param> /// <param>DataTable的列名是否要导入</param> /// <param>要导入的excel的sheet的名称</param> /// <param>文件夹路径</param> /// <returns>导入数据行数(包含列名那一行)</returns> public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName) { if (data == null) { throw new ArgumentNullException("data"); } if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } IWorkbook workbook = null; if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(); } FileStream fs = null; try { fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); ISheet sheet; if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } int j; int count; //写入DataTable的列名,写入单元格中 if (isColumnWritten) { var row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } //遍历循环datatable具体数据项 int i; for (i = 0; i < data.Rows.Count; ++i) { var row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } //将文件流写入到excel workbook.Write(fs); return count; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } }
3.将excel中的数据导入到DataTable中: