这里有新鲜出炉的精品教程,程序狗速度看过来!
.NET 是 Microsoft XML Web services 平台。你可以理解为.NET Framework ,XML Web services 允许应用程序通过 Internet 进行通讯和共享数据,而不管所采用的是哪种操作系统、设备或编程语言。Microsoft .NET 平台提供创建 XML Web services 并将这些服务集成在一起之所需。对个人用户的好处是无缝的、吸引人的体验。
这篇文章主要介绍了详解免费高效实用的.NET操作Excel组件NPOI(.NET组件介绍之六),具有一定的参考价值,有兴趣的可以了解一下。
很多的软件项目几乎都包含着对文档的操作,前面已经介绍过两款操作文档的组件,现在介绍一款文档操作的组件NPOI。
NPOI可以生成没有安装在您的服务器上的Microsoft Office套件的Excel报表,并且在后台调用Microsoft Excel ActiveX更有效率;从Office文档中提取文本,以帮助您实现全文索引功能(大多数时候,此功能用于创建搜索引擎); 从Office文档提取图像; 生成包含公式的Excel工作表。
一.NPOI组件概述:
NPOI是完全免费使用; 涵盖Excel的大多数功能(单元格样式,数据格式,公式等);支持xls,xlsx,docx;设计为面向接口(看看NPOI.SS命名空间);支持不仅导出而且导入; .Net 2.0甚至为xlsx和docx(虽然我们也支持.NET 4.0); 来自世界各地的成功案例;巨大的基本例子;对隔离存储没有依赖。
以上是NPOI的优点,其他一些优点可以不用太在意,估计很多人对“支持xls,xlsx,docx”这一特点感觉有些惊讶,因为在很多人的印象里面NPOI就是对Excel进行相关的操作,但是在这里突然看到了对docx也可以操作,这一特点可能让很多人感到欣喜,因为NPOI的的确确是一个很不错的组件,用过的人都说好,我也不例外。
NPOI的运行要求:VS2010与.NET 4.0运行时;VS2005或VS2008与.NET 2.0运行时(SP1);vs2003与.NET 1.1;Mono;ASP.NET中的中等信任环境。
二.NPOI核心类和方法解析:
以上是对NPOI的相关背景和使用环境做了一个简单的介绍,接下来我具体的看一下NPOI的一些核心类和方法,由于下载的是DLL文件,还是采用.NET Reflector对DLL文件进行反编译,以此查看源代码。
如果需要具体的了解NPOI可以直接访问:http://npoi.codeplex.com/SourceControl/latest,提供了NPOI的源码和一些demo,由于本溪介绍的重点是NPOI对Excel的操作,所以下面的类和实例主要是对操作Excel的介绍,如果需要对docx的操作,可以具体查看相应的类demo。
1.XSSFWorkbook类CreateSheet():创建表。
- 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;
- }
2.XSSFSheet类Write():将文件流写入到excel。
- 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<string, string> dictionary = new Dictionary<string, string>();
- dictionary[ST_RelationshipId.NamespaceURI] = "r";
- new WorksheetDocument(this.worksheet).Save(stream);
- if (flag)
- {
- this.worksheet.AddNewCols();
- }
- }
3.XSSFSheet类CreateRow():创建行。
- 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;
- }
4.XSSFWorkbook类GetSheet:获取表。
- 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 name="data">要导入的数据</param>
- /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
- /// <param name="sheetName">要导入的excel的sheet的名称</param>
- /// <param name="fileName">文件夹路径</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中:
- /// <summary>
- /// 将excel中的数据导入到DataTable中
- /// </summary>
- /// <param name="sheetName">excel工作薄sheet的名称</param>
- /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
- /// <param name="fileName">文件路径</param>
- /// <returns>返回的DataTable</returns>
- 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, FileAccess.Read);
- if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
- {
- workbook = new XSSFWorkbook(fs);
- }
- else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
- {
- workbook = new HSSFWorkbook(fs);
- }
- ISheet sheet = null;
- if (workbook != null)
- {
- //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
- sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
- }
- if (sheet == null) return data;
- var firstRow = sheet.GetRow(0);
- //一行最后一个cell的编号 即总的列数
- int cellCount = firstRow.LastCellNum;
- int startRow;
- if (isFirstRowColumn)
- {
- for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
- {
- var cell = firstRow.GetCell(i);
- var cellValue = cell.StringCellValue;
- if (cellValue == null) continue;
- var column = new DataColumn(cellValue);
- data.Columns.Add(column);
- }
- startRow = sheet.FirstRowNum + 1;
- }
- else
- {
- startRow = sheet.FirstRowNum;
- }
- //最后一列的标号
- var rowCount = sheet.LastRowNum;
- for (var i = startRow; i <= rowCount; ++i)
- {
- var row = sheet.GetRow(i);
- //没有数据的行默认是null
- if (row == null) continue;
- var dataRow = data.NewRow();
- for (int j = row.FirstCellNum; j < cellCount; ++j)
- {
- //同理,没有数据的单元格都默认是null
- if (row.GetCell(j) != null)
- dataRow[j] = row.GetCell(j).ToString();
- }
- data.Rows.Add(dataRow);
- }
- return data;
- }
- catch (IOException ioex)
- {
- throw new IOException(ioex.Message);
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- finally
- {
- if (fs != null)
- {
- fs.Close();
- }
- }
- }
4.读取Excel文件内容转换为DataSet:
- /// <summary>
- /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
- /// </summary>
- /// <param name="fileName">文件绝对路径</param>
- /// <param name="startRow">数据开始行数(1为第一行)</param>
- /// <param name="columnDataType">每列的数据类型</param>
- /// <returns></returns>
- public static DataSet ReadExcel(string fileName, int startRow, params NpoiDataType[] columnDataType)
- {
- var ds = new DataSet("ds");
- var dt = new DataTable("dt");
- var sb = new StringBuilder();
- using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
- {
- //使用接口,自动识别excel2003/2007格式
- var workbook = WorkbookFactory.Create(stream);
- //得到里面第一个sheet
- var sheet = workbook.GetSheetAt(0);
- int j;
- IRow row;
- //ColumnDataType赋值
- if (columnDataType.Length <= 0)
- {
- //得到第i行
- row = sheet.GetRow(startRow - 1);
- columnDataType = new NpoiDataType[row.LastCellNum];
- for (var i = 0; i < row.LastCellNum; i++)
- {
- var hs = row.GetCell(i);
- columnDataType[i] = GetCellDataType(hs);
- }
- }
- for (j = 0; j < columnDataType.Length; j++)
- {
- var tp = GetDataTableType(columnDataType[j]);
- dt.Columns.Add("c" + j, tp);
- }
- for (var i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
- {
- //得到第i行
- row = sheet.GetRow(i);
- if (row == null) continue;
- try
- {
- var dr = dt.NewRow();
- for (j = 0; j < columnDataType.Length; j++)
- {
- dr["c" + j] = GetCellData(columnDataType[j], row, j);
- }
- dt.Rows.Add(dr);
- }
- catch (Exception er)
- {
- sb.Append(string.Format("第{0}行出错:{1}\r\n", i + 1, er.Message));
- }
- }
- ds.Tables.Add(dt);
- }
- if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
- return ds;
- }
5.从DataSet导出到2003:
- /// <summary>
- /// 从DataSet导出到MemoryStream流2003
- /// </summary>
- /// <param name="saveFileName">文件保存路径</param>
- /// <param name="sheetName">Excel文件中的Sheet名称</param>
- /// <param name="ds">存储数据的DataSet</param>
- /// <param name="startRow">从哪一行开始写入,从0开始</param>
- /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
- public static bool CreateExcel2003(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
- {
- try
- {
- if (startRow < 0) startRow = 0;
- var wb = new HSSFWorkbook();
- var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
- dsi.Company = "pkm";
- var si = PropertySetFactory.CreateSummaryInformation();
- si.Title =
- si.Subject = "automatic genereted document";
- si.Author = "pkm";
- wb.DocumentSummaryInformation = dsi;
- wb.SummaryInformation = si;
- var sheet = wb.CreateSheet(sheetName);
- //sheet.SetColumnWidth(0, 50 * 256);
- //sheet.SetColumnWidth(1, 100 * 256);
- ICell cell;
- int j;
- var maxLength = 0;
- var curLength = 0;
- object columnValue;
- var dt = ds.Tables[0];
- if (datatypes.Length < dt.Columns.Count)
- {
- datatypes = new NpoiDataType[dt.Columns.Count];
- for (var i = 0; i < dt.Columns.Count; i++)
- {
- var dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
- switch (dtcolumntype)
- {
- case "string":
- datatypes[i] = NpoiDataType.String;
- break;
- case "datetime":
- datatypes[i] = NpoiDataType.Datetime;
- break;
- case "boolean":
- datatypes[i] = NpoiDataType.Bool;
- break;
- case "double":
- datatypes[i] = NpoiDataType.Numeric;
- break;
- default:
- datatypes[i] = NpoiDataType.String;
- break;
- }
- }
- }
- // 创建表头
- var row = sheet.CreateRow(0);
- //样式
- var style1 = wb.CreateCellStyle();
- //字体
- var font1 = wb.CreateFont();
- //字体颜色
- font1.Color = HSSFColor.White.Index;
- //字体加粗样式
- font1.Boldweight = (short)FontBoldWeight.Bold;
- //style1.FillBackgroundColor = HSSFColor.WHITE.index;
- style1.FillForegroundColor = HSSFColor.Green.Index;
- //GetXLColour(wb, LevelOneColor);// 设置图案色
- //GetXLColour(wb, LevelOneColor);// 设置背景色
- style1.FillPattern = FillPattern.SolidForeground;
- //样式里的字体设置具体的字体样式
- style1.SetFont(font1);
- //文字水平对齐方式
- style1.Alignment = HorizontalAlignment.Center;
- //文字垂直对齐方式
- style1.VerticalAlignment = VerticalAlignment.Center;
- row.HeightInPoints = 25;
- for (j = 0; j < dt.Columns.Count; j++)
- {
- columnValue = dt.Columns[j].ColumnName;
- curLength = Encoding.Default.GetByteCount(columnValue.ToString());
- maxLength = (maxLength < curLength ? curLength : maxLength);
- var colounwidth = 256 * maxLength;
- sheet.SetColumnWidth(j, colounwidth);
- try
- {
- //创建第0行的第j列
- cell = row.CreateCell(j);
- //单元格式设置样式
- cell.CellStyle = style1;
- try
- {
- cell.SetCellType(CellType.String);
- cell.SetCellValue(columnValue.ToString());
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- // 创建每一行
- for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
- {
- var dr = ds.Tables[0].Rows[i];
- //创建第i行
- row = sheet.CreateRow(i + 1);
- for (j = 0; j < dt.Columns.Count; j++)
- {
- columnValue = dr[j];
- curLength = Encoding.Default.GetByteCount(columnValue.ToString());
- maxLength = (maxLength < curLength ? curLength : maxLength);
- var colounwidth = 256 * maxLength;
- sheet.SetColumnWidth(j, colounwidth);
- try
- {
- //创建第i行的第j列
- cell = row.CreateCell(j);
- // 插入第j列的数据
- try
- {
- var dtype = datatypes[j];
- switch (dtype)
- {
- case NpoiDataType.String:
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- case NpoiDataType.Datetime:
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- case NpoiDataType.Numeric:
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(Convert.ToDouble(columnValue));
- }
- break;
- case NpoiDataType.Bool:
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(Convert.ToBoolean(columnValue));
- }
- break;
- case NpoiDataType.Richtext:
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- }
- }
- catch (Exception ex)
- {
- cell.SetCellType(CellType.Numeric);
- cell.SetCellValue(columnValue.ToString());
- throw new Exception(ex.Message);
- }
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- }
- //生成文件在服务器上
- using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
- {
- wb.Write(fs);
- }
- return true;
- }
- catch (Exception er)
- {
- throw new Exception(er.Message);
- }
- }
6.从DataSet导出到MemoryStream流2007:
- /// <summary>
- /// 从DataSet导出到MemoryStream流2007
- /// </summary>
- /// <param name="saveFileName">文件保存路径</param>
- /// <param name="sheetName">Excel文件中的Sheet名称</param>
- /// <param name="ds">存储数据的DataSet</param>
- /// <param name="startRow">从哪一行开始写入,从0开始</param>
- /// <param name="datatypes">DataSet中的各列对应的数据类型</param>
- public static bool CreateExcel2007(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
- {
- try
- {
- if (startRow < 0) startRow = 0;
- var wb = new XSSFWorkbook();
- var sheet = wb.CreateSheet(sheetName);
- ICell cell;
- int j;
- var maxLength = 0;
- int curLength;
- object columnValue;
- var dt = ds.Tables[0];
- if (datatypes.Length < dt.Columns.Count)
- {
- datatypes = new NpoiDataType[dt.Columns.Count];
- for (var i = 0; i < dt.Columns.Count; i++)
- {
- var dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
- switch (dtcolumntype)
- {
- case "string":
- datatypes[i] = NpoiDataType.String;
- break;
- case "datetime":
- datatypes[i] = NpoiDataType.Datetime;
- break;
- case "boolean":
- datatypes[i] = NpoiDataType.Bool;
- break;
- case "double":
- datatypes[i] = NpoiDataType.Numeric;
- break;
- default:
- datatypes[i] = NpoiDataType.String;
- break;
- }
- }
- }
- //创建表头
- var row = sheet.CreateRow(0);
- //样式
- var style1 = wb.CreateCellStyle();
- //字体
- var font1 = wb.CreateFont();
- //字体颜色
- font1.Color = HSSFColor.White.Index;
- //字体加粗样式
- font1.Boldweight = (short)FontBoldWeight.Bold;
- //style1.FillBackgroundColor = HSSFColor.WHITE.index;
- //GetXLColour(wb, LevelOneColor);
- // 设置图案色
- style1.FillForegroundColor = HSSFColor.Green.Index;
- //GetXLColour(wb, LevelOneColor);// 设置背景色
- style1.FillPattern = FillPattern.SolidForeground;
- //样式里的字体设置具体的字体样式
- style1.SetFont(font1);
- //文字水平对齐方式
- style1.Alignment = HorizontalAlignment.Center;
- //文字垂直对齐方式
- style1.VerticalAlignment = VerticalAlignment.Center;
- row.HeightInPoints = 25;
- for (j = 0; j < dt.Columns.Count; j++)
- {
- columnValue = dt.Columns[j].ColumnName;
- curLength = Encoding.Default.GetByteCount(columnValue.ToString());
- maxLength = (maxLength < curLength ? curLength : maxLength);
- var colounwidth = 256 * maxLength;
- sheet.SetColumnWidth(j, colounwidth);
- try
- {
- //创建第0行的第j列
- cell = row.CreateCell(j);
- //单元格式设置样式
- cell.CellStyle = style1;
- try
- {
- cell.SetCellValue(columnValue.ToString());
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- // 创建每一行
- for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
- {
- var dr = ds.Tables[0].Rows[i];
- //创建第i行
- row = sheet.CreateRow(i + 1);
- for (j = 0; j < dt.Columns.Count; j++)
- {
- columnValue = dr[j];
- curLength = Encoding.Default.GetByteCount(columnValue.ToString());
- maxLength = (maxLength < curLength ? curLength : maxLength);
- var colounwidth = 256 * maxLength;
- sheet.SetColumnWidth(j, colounwidth);
- try
- {
- //创建第i行的第j列
- cell = row.CreateCell(j);
- // 插入第j列的数据
- try
- {
- var dtype = datatypes[j];
- switch (dtype)
- {
- case NpoiDataType.String:
- {
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- case NpoiDataType.Datetime:
- {
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- case NpoiDataType.Numeric:
- {
- cell.SetCellValue(Convert.ToDouble(columnValue));
- }
- break;
- case NpoiDataType.Bool:
- {
- cell.SetCellValue(Convert.ToBoolean(columnValue));
- }
- break;
- case NpoiDataType.Richtext:
- {
- cell.SetCellValue(columnValue.ToString());
- }
- break;
- }
- }
- catch (Exception ex)
- {
- cell.SetCellValue(columnValue.ToString());
- throw new Exception(ex.Message);
- }
- }
- catch (Exception ex)
- {
- throw new Exception(ex.Message);
- }
- }
- }
- //生成文件在服务器上
- using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
- {
- wb.Write(fs);
- }
- return true;
- }
- catch (Exception er)
- {
- throw new Exception(er.Message);
- }
- }
7.读Excel-根据NpoiDataType创建的DataTable列的数据类型:
- /// <summary>
- /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
- /// </summary>
- /// <param name="datatype"></param>
- /// <returns></returns>
- private static Type GetDataTableType(NpoiDataType datatype) {
- var tp = typeof(string);
- switch (datatype) {
- case NpoiDataType.Bool:
- tp = typeof(bool);
- break;
- case NpoiDataType.Datetime:
- tp = typeof(DateTime);
- break;
- case NpoiDataType.Numeric:
- tp = typeof(double);
- break;
- case NpoiDataType.Error:
- tp = typeof(string);
- break;
- case NpoiDataType.Blank:
- tp = typeof(string);
- break;
- }
- return tp;
- }
- /// <summary>
- /// 读Excel-得到不同数据类型单元格的数据
- /// </summary>
- /// <param name="datatype">数据类型</param>
- /// <param name="row">数据中的一行</param>
- /// <param name="column">哪列</param>
- /// <returns></returns>
- private static object GetCellData(NpoiDataType datatype, IRow row, int column) {
- switch (datatype) {
- case NpoiDataType.String:
- try {
- return row.GetCell(column).DateCellValue;
- } catch {
- try {
- return row.GetCell(column).StringCellValue;
- } catch {
- return row.GetCell(column).NumericCellValue;
- }
- }
- case NpoiDataType.Bool:
- try {
- return row.GetCell(column).BooleanCellValue;
- } catch {
- return row.GetCell(column).StringCellValue;
- }
- case NpoiDataType.Datetime:
- try {
- return row.GetCell(column).DateCellValue;
- } catch {
- return row.GetCell(column).StringCellValue;
- }
- case NpoiDataType.Numeric:
- try {
- return row.GetCell(column).NumericCellValue;
- } catch {
- return row.GetCell(column).StringCellValue;
- }
- case NpoiDataType.Richtext:
- try {
- return row.GetCell(column).RichStringCellValue;
- } catch {
- return row.GetCell(column).StringCellValue;
- }
- case NpoiDataType.Error:
- try {
- return row.GetCell(column).ErrorCellValue;
- } catch {
- return row.GetCell(column).StringCellValue;
- }
- case NpoiDataType.Blank:
- try {
- return row.GetCell(column).StringCellValue;
- } catch {
- return "";
- }
- default:
- return "";
- }
- }
- /// <summary>
- /// 获取单元格数据类型
- /// </summary>
- /// <param name="hs">单元格对象</param>
- /// <returns></returns>
- private static NpoiDataType GetCellDataType(ICell hs) {
- NpoiDataType dtype;
- DateTime t1;
- var cellvalue = "";
- switch (hs.CellType) {
- case CellType.Blank:
- dtype = NpoiDataType.String;
- cellvalue = hs.StringCellValue;
- break;
- case CellType.Boolean:
- dtype = NpoiDataType.Bool;
- break;
- case CellType.Numeric:
- dtype = NpoiDataType.Numeric;
- cellvalue = hs.NumericCellValue.ToString(CultureInfo.InvariantCulture);
- break;
- case CellType.String:
- dtype = NpoiDataType.String;
- cellvalue = hs.StringCellValue;
- break;
- case CellType.Error:
- dtype = NpoiDataType.Error;
- break;
- default:
- dtype = NpoiDataType.Datetime;
- break;
- }
- if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
- return dtype;
- }
四.总结:
本文是接着上五篇介绍.NET组件,目的只是在于总结一些组件的用法,将文章作为一个引子,各位读者可以根据文章的介绍更加深入的去了解相关组件。有些地方写的有误,还望多多包涵和指正,欢迎大家给我建议介绍一些你们在项目中经常使用的组件,可以跟大家做一个分享。
来源: http://www.phperz.com/article/17/0904/338108.html