详解免费高效实用的.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 dictionary = new Dictionary();
  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单元格数据类型)：

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

2. 将DataTable数据导入到excel中：

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

3.将excel中的数据导入到DataTable中：

  /// 
    /// 将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, 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：

  /// 
    /// 读取Excel文件内容转换为DataSet,列名依次为 "c0"……c[columnlength-1]
    /// 
    /// 文件绝对路径
    /// 数据开始行数(1为第一行)
    /// 每列的数据类型
    /// 
    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}rn", 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：

  /// 
    /// 从DataSet导出到MemoryStream流2003
    /// 
    /// 文件保存路径
    /// Excel文件中的Sheet名称
    /// 存储数据的DataSet
    /// 从哪一行开始写入，从0开始
    /// DataSet中的各列对应的数据类型
    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：

/// 
    /// 从DataSet导出到MemoryStream流2007
    /// 
    /// 文件保存路径
    /// Excel文件中的Sheet名称
    /// 存储数据的DataSet
    /// 从哪一行开始写入，从0开始
    /// DataSet中的各列对应的数据类型
    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列的数据类型：

 /// 
    /// 读Excel-根据NpoiDataType创建的DataTable列的数据类型
    /// 
    /// 
    /// 
    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;
    }


    /// 
    /// 读Excel-得到不同数据类型单元格的数据
    /// 
    /// 数据类型
    /// 数据中的一行
    /// 哪列
    /// 
    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 "";
      }
    }

    /// 
    /// 获取单元格数据类型
    /// 
    /// 单元格对象
    /// 
    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组件，目的只是在于总结一些组件的用法，将文章作为一个引子，各位读者可以根据文章的介绍更加深入的去了解相关组件。有些地方写的有误，还望多多包涵和指正，欢迎大家给我建议介绍一些你们在项目中经常使用的组件，可以跟大家做一个分享。

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

asp相关栏目本月热门文章