詳解免費高效實用的.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<string, string> dictionary = new Dictionary<string, string>();
 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);
 }
}

 /// <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
 }

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

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

 /// <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;
 }

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

 }

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

 }

 /// <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;
 }