服务器之家

服务器之家 > 正文

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

时间：2020-04-11 14:58     来源/作者：彭泽0902

很多的软件项目几乎都包含着对文档的操作，前面已经介绍过两款操作文档的组件，现在介绍一款文档操作的组件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。

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

1.XSSFWorkbook类CreateSheet():创建表。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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。
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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()：创建行。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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：获取表。

?
1
2
3
4
5
6
7
8
9
10
11
public ISheet GetSheet(string name)
{
  foreach (XSSFSheet sheet in this.sheets)
  {
    if (name.Equals(sheet.SheetName, StringComparison.InvariantCultureIgnoreCase))
    {
      return sheet;
    }
  }
  return null;
}

5.WorkbookFactory类：

?
1
2
3
4
5
6
7
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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[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; }
}

具体方法：

?
1
2
3
4
5
6
7
8
9
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单元格数据类型)：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/// <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中：

    

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
/// <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中：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
/// <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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
/// <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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
/// <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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
/// <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列的数据类型：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
/// <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.cnblogs.com/pengze0902/p/6150070.html

标签：
Excel .NET npoi 

相关文章

热门资讯

沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思？女生说13579是什么意思？
男生常说24816是什么意思？女生说13579是什么意思？ 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部