详解免费高效实用的.NET操作Excel组件NPOI（.NET组件介绍之六）_实用技巧_

 public ISheet CreateSheet(string sheetname) { if (sheetname == null) { throw new ArgumentException("sheetName must not be null"); } if (this.ContainsSheet(sheetname, this.sheets.Count)) { throw new ArgumentException("The workbook already contains a sheet of this name"); } if (sheetname.Length > 0x1f) { sheetname = sheetname.Substring(0, 0x1f); } WorkbookUtil.ValidateSheetName(sheetname); CT_Sheet sheet = this.AddSheet(sheetname); int index = 1; foreach (XSSFSheet sheet2 in this.sheets) { index = (int) Math.Max((long) (sheet2.sheet.sheetId + 1), (long) index); } Label_0099: foreach (XSSFSheet sheet3 in this.sheets) { index = (int) Math.Max((long) (sheet3.sheet.sheetId + 1), (long) index); } string fileName = XSSFRelation.WORKSHEET.GetFileName(index); foreach (POIXMLDocumentPart part in base.GetRelations()) { if ((part.GetPackagePart() != null) && fileName.Equals(part.GetPackagePart().PartName.Name)) { index++; goto Label_0099; } } XSSFSheet item = (XSSFSheet) base.CreateRelationship(XSSFRelation.WORKSHEET, XSSFFactory.GetInstance(), index); item.sheet = sheet; sheet.id = item.GetPackageRelationship().Id; sheet.sheetId = (uint) index; if (this.sheets.Count == 0) { item.IsSelected = true; } this.sheets.Add(item); return item; } 

 internal virtual void Write(Stream stream) { bool flag = false; if (this.worksheet.sizeOfColsArray() == 1) { CT_Cols colsArray = this.worksheet.GetColsArray(0); if (colsArray.sizeOfColArray() == 0) { flag = true; this.worksheet.SetColsArray(null); } else { this.SetColWidthAttribute(colsArray); } } if (this.hyperlinks.Count > 0) { if (this.worksheet.hyperlinks == null) { this.worksheet.AddNewHyperlinks(); } CT_Hyperlink[] array = new CT_Hyperlink[this.hyperlinks.Count]; for (int i = 0; i < array.Length; i++) { XSSFHyperlink hyperlink = this.hyperlinks[i]; hyperlink.GenerateRelationIfNeeded(base.GetPackagePart()); array[i] = hyperlink.GetCTHyperlink(); } this.worksheet.hyperlinks.SetHyperlinkArray(array); } foreach (XSSFRow row in this._rows.Values) { row.OnDocumentWrite(); } Dictionary dictionary = new Dictionary(); dictionary[ST_RelationshipId.NamespaceURI] = "r"; new WorksheetDocument(this.worksheet).Save(stream); if (flag) { this.worksheet.AddNewCols(); } } 

 public virtual IRow CreateRow(int rownum) { CT_Row cTRow; XSSFRow row2 = this._rows.ContainsKey(rownum) ? this._rows[rownum] : null; if (row2 != null) { cTRow = row2.GetCTRow(); cTRow.Set(new CT_Row()); } else if ((this._rows.Count == 0) || (rownum > this.GetLastKey(this._rows.Keys))) { cTRow = this.worksheet.sheetData.AddNewRow(); } else { int count = this.HeadMap(this._rows, rownum).Count; cTRow = this.worksheet.sheetData.InsertNewRow(count); } XSSFRow row3 = new XSSFRow(cTRow, this) { RowNum = rownum }; this._rows[rownum] = row3; return row3; } 

 public ISheet GetSheet(string name) { foreach (XSSFSheet sheet in this.sheets) { if (name.Equals(sheet.SheetName, StringComparison.InvariantCultureIgnoreCase)) { return sheet; } } return null; } 

 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(); } 

 [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); } } 

 /// 
 /// 枚举(Excel单元格数据类型) /// 
 public enum NpoiDataType { /// 
 /// 字符串类型-值为1 /// 
 String, /// 
 /// 布尔类型-值为2 /// 
 Bool, /// 
 /// 时间类型-值为3 /// 
 Datetime, /// 
 /// 数字类型-值为4 /// 
 Numeric, /// 
 /// 复杂文本类型-值为5 /// 
 Richtext, /// 
 /// 空白 /// 
 Blank, /// 
 /// 错误 /// 
 Error } 

 /// 
 /// 将DataTable数据导入到excel中 /// 
 /// 要导入的数据 /// DataTable的列名是否要导入 /// 要导入的excel的sheet的名称 /// 文件夹路径 /// 导入数据行数(包含列名那一行) 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(); } } } 

 /// 
 /// 将excel中的数据导入到DataTable中 /// 
 /// excel工作薄sheet的名称 /// 第一行是否是DataTable的列名 /// 文件路径 /// 返回的DataTable public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(sheetName); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAcce
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：ASP.NET用SignalR建立浏览器和服务器的持久连接详解_实用技巧_
                                                                
下一篇：详解免费开源的DotNet任务调度组件Quartz.NET（.NET组件介绍之五）_实用技巧_