NPOI设置列宽、行高、下拉列表等信息

原创 Laughing  2018-05-16 23:17  阅读 558 次 评论 0 条
  1. using Genersoft.Platform.Controls.WinForms;  
  2. using NPOI.SS.UserModel;  
  3. using NPOI.XSSF.UserModel;  
  4. using NPOI.HSSF.UserModel;  
  5. using System;  
  6. using System.Collections.Generic;  
  7. using System.Data;  
  8. using System.IO;  
  9. using System.Linq;  
  10. using System.Text;  
  11. using System.Collections;  
  12. using Genersoft.GS.HIS.ZW.SPI;  
  13. using NPOI.HSSF.Util;  
  14. using NPOI.SS.Util;  
  15.   
  16. namespace Genersoft.GS.HIS.ZW.Controller  
  17. {  
  18.     public class ExcelHelper : IDisposable  
  19.     {  
  20.         #region 变量属性  
  21.         private IWorkbook workbook = null;  
  22.         private FileStream fs = null;  
  23.         private bool disposed;  
  24.         private string fileName = string.Empty;  
  25.         #endregion  
  26.  
  27.         #region 构造函数  
  28.         public ExcelHelper()  
  29.         {  
  30.             disposed = false;  
  31.         }  
  32.         #endregion  
  33.  
  34.         #region 方法  
  35.  
  36.         #region 将DataTable数据导入到excel中  
  37.         /// <summary>  
  38.         /// 将DataTable数据导入到excel中  
  39.         /// </summary>  
  40.         /// <param name="data">要导入的数据</param>  
  41.         /// <param name="isColumnWritten">DataTable的列名是否要导入</param>  
  42.         /// <param name="sheetName">要导入的excel的sheet的名称</param>  
  43.         /// <returns>导入数据行数(包含列名那一行)</returns>  
  44.         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, out string fileName)  
  45.         {  
  46.             int i = 0;  
  47.             int j = 0;  
  48.             int count = 0;  
  49.             ISheet sheet = null;  
  50.             fileName = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop) + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; ; //文件名 
  51.             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 
  52.             if (fileName.IndexOf(".xlsx") > 0) // 2007版本 
  53.                 workbook = new XSSFWorkbook(); 
  54.             else if (fileName.IndexOf(".xls") > 0) // 2003版本 
  55.                 workbook = new HSSFWorkbook(); 
  56.  
  57.             try 
  58.             { 
  59.                 if (workbook != null) 
  60.                 { 
  61.                     sheet = workbook.CreateSheet(sheetName); 
  62.                 } 
  63.                 else 
  64.                 { 
  65.                     return -1; 
  66.                 } 
  67.  
  68.                 if (isColumnWritten == true) //写入DataTable的列名 
  69.                 { 
  70.                     IRow row = sheet.CreateRow(0); 
  71.                     for (j = 0; j < data.Columns.Count; ++j) 
  72.                     { 
  73.                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 
  74.                     } 
  75.                     count = 1; 
  76.                 } 
  77.                 else 
  78.                 { 
  79.                     count = 0; 
  80.                 } 
  81.  
  82.                 for (i = 0; i < data.Rows.Count; ++i) 
  83.                 { 
  84.                     IRow row = sheet.CreateRow(count); 
  85.                     for (j = 0; j < data.Columns.Count; ++j) 
  86.                     { 
  87.                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 
  88.                     } 
  89.                     ++count; 
  90.                 } 
  91.                 workbook.Write(fs); //写入到excel 
  92.                 fs.Close(); 
  93.                 return count; 
  94.             } 
  95.             catch (Exception ex) 
  96.             { 
  97.                 Console.WriteLine("Exception: " + ex.Message); 
  98.                 return -1; 
  99.             } 
  100.         } 
  101.         #endregion 
  102.  
  103.         #region 将excel中的数据导入到DataTable中 
  104.         /// <summary> 
  105.         /// 将excel中的数据导入到DataTable中 
  106.         /// </summary> 
  107.         /// <param name="sheetName">excel工作薄sheet的名称</param> 
  108.         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 
  109.         /// <returns>返回的DataTable</returns> 
  110.         public DataSet ExcelToDataTable(string sheetName, bool isFirstRowColumn, string excelPath) 
  111.         { 
  112.             DataSet ds = new DataSet(); 
  113.             ISheet sheet = null; 
  114.             DataTable data = new DataTable(); 
  115.             int startRow = 0; 
  116.             try 
  117.             { 
  118.                 fs = new FileStream(excelPath, FileMode.Open); 
  119.                 long left = fs.Length; 
  120.                 byte[] bytes = new byte[1024]; 
  121.                 int maxLength = bytes.Length; 
  122.                 int start = 0; 
  123.                 int num = 0; 
  124.                 while (left > 0) 
  125.                 { 
  126.                     fs.Position = start; 
  127.                     num = 0; 
  128.                     if (left < maxLength) 
  129.                         num = fs.Read(bytes, 0, Convert.ToInt32(left)); 
  130.                     else 
  131.                         num = fs.Read(bytes, 0, maxLength); 
  132.                     if (num == 0) 
  133.                         break; 
  134.                     start += num; 
  135.                     left -= num; 
  136.                 } 
  137.                 fs.Seek(0, SeekOrigin.Begin); 
  138.                 if (excelPath.IndexOf(".xlsx") > 0) // 2007版本 
  139.                     workbook = new XSSFWorkbook(fs); 
  140.                 else if (excelPath.IndexOf(".xls") > 0) // 2003版本 
  141.                     workbook = new HSSFWorkbook(fs); 
  142.                 if (sheetName != null) 
  143.                 { 
  144.                     sheet = workbook.GetSheet(sheetName); 
  145.                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet 
  146.                     { 
  147.                         sheet = workbook.GetSheetAt(0); 
  148.                     } 
  149.                 } 
  150.                 else 
  151.                 { 
  152.                     sheet = workbook.GetSheetAt(0); 
  153.                 } 
  154.                 if (sheet != null) 
  155.                 { 
  156.                     IRow firstRow = sheet.GetRow(0); 
  157.                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 
  158.  
  159.                     if (isFirstRowColumn) 
  160.                     { 
  161.                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 
  162.                         { 
  163.                             ICell cell = firstRow.GetCell(i); 
  164.                             if (cell != null) 
  165.                             { 
  166.                                 string cellValue = cell.StringCellValue; 
  167.                                 if (cellValue != null) 
  168.                                 { 
  169.                                     DataColumn column = new DataColumn(cellValue); 
  170.                                     data.Columns.Add(column); 
  171.                                 } 
  172.                             } 
  173.                         } 
  174.                         startRow = sheet.FirstRowNum + 1; 
  175.                     } 
  176.                     else 
  177.                     { 
  178.                         startRow = sheet.FirstRowNum; 
  179.                     } 
  180.  
  181.                     //最后一列的标号 
  182.                     int rowCount = sheet.LastRowNum; 
  183.                     for (int i = startRow; i <= rowCount; ++i) 
  184.                     { 
  185.                         IRow row = sheet.GetRow(i); 
  186.                         if (row == null) continue; //没有数据的行默认是null  
  187.  
  188.                         //处理空行数据 
  189.                         bool isBlankRow = true; 
  190.                         for (int j = row.FirstCellNum; j < cellCount; ++j) 
  191.                         { 
  192.                             if (row.GetCell(j) != null) //对象实例不为null 
  193.                             { 
  194.                                 if (!string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))//判断单元格是否为空 
  195.                                 { 
  196.                                     isBlankRow = false; 
  197.                                 } 
  198.  
  199.                             } 
  200.                         } 
  201.                         if (isBlankRow) 
  202.                         { 
  203.                             continue; 
  204.                         } 
  205.  
  206.                         //增加行数据 
  207.                         DataRow dataRow = data.NewRow(); 
  208.                         for (int j = row.FirstCellNum; j < cellCount; ++j) 
  209.                         { 
  210.                             if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null 
  211.                                 dataRow[j] = row.GetCell(j).ToString(); 
  212.                         } 
  213.                         data.Rows.Add(dataRow); 
  214.                     } 
  215.                 } 
  216.                 if (data != null) 
  217.                 { 
  218.                     DataView dv = data.DefaultView; 
  219.                     dv.Sort = "合同编号 Asc"; 
  220.                     DataTable dtSort = dv.ToTable(); 
  221.                     ds.Tables.Add(dtSort); 
  222.                     return ds; 
  223.                 } 
  224.                 else 
  225.                 { 
  226.                     return null; 
  227.                 } 
  228.  
  229.             } 
  230.             catch (Exception ex) 
  231.             { 
  232.                 UMessageBox.Information(ex.Message); 
  233.                 return null; 
  234.             } 
  235.         } 
  236.         #endregion 
  237.  
  238.         #region 将excel中的数据导入到DataTable中 
  239.         /// <summary> 
  240.         /// 将excel中的数据导入到DataTable中 
  241.         /// </summary> 
  242.         /// <param name="sheetName">excel工作薄sheet的名称</param> 
  243.         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 
  244.         /// <returns>返回的DataTable</returns> 
  245.         public DataSet BDExcelToDataTable(string sheetName, bool isFirstRowColumn, string excelPath) 
  246.         { 
  247.             DataSet ds = new DataSet(); 
  248.             ISheet sheet = null; 
  249.             DataTable data = new DataTable(); 
  250.             int startRow = 0; 
  251.             try 
  252.             { 
  253.                 fs = new FileStream(excelPath, FileMode.Open); 
  254.                 long left = fs.Length; 
  255.                 byte[] bytes = new byte[1024]; 
  256.                 int maxLength = bytes.Length; 
  257.                 int start = 0; 
  258.                 int num = 0; 
  259.                 while (left > 0) 
  260.                 { 
  261.                     fs.Position = start; 
  262.                     num = 0; 
  263.                     if (left < maxLength) 
  264.                         num = fs.Read(bytes, 0, Convert.ToInt32(left)); 
  265.                     else 
  266.                         num = fs.Read(bytes, 0, maxLength); 
  267.                     if (num == 0) 
  268.                         break; 
  269.                     start += num; 
  270.                     left -= num; 
  271.                 } 
  272.                 fs.Seek(0, SeekOrigin.Begin); 
  273.                 if (excelPath.IndexOf(".xlsx") > 0) // 2007版本 
  274.                     workbook = new XSSFWorkbook(fs); 
  275.                 else if (excelPath.IndexOf(".xls") > 0) // 2003版本 
  276.                     workbook = new HSSFWorkbook(fs); 
  277.                 if (sheetName != null) 
  278.                 { 
  279.                     sheet = workbook.GetSheet(sheetName); 
  280.                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet 
  281.                     { 
  282.                         sheet = workbook.GetSheetAt(0); 
  283.                     } 
  284.                 } 
  285.                 else 
  286.                 { 
  287.                     sheet = workbook.GetSheetAt(0); 
  288.                 } 
  289.                 if (sheet != null) 
  290.                 { 
  291.                     IRow firstRow = sheet.GetRow(0); 
  292.                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 
  293.  
  294.                     if (isFirstRowColumn) 
  295.                     { 
  296.                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 
  297.                         { 
  298.                             ICell cell = firstRow.GetCell(i); 
  299.                             if (cell != null) 
  300.                             { 
  301.                                 string cellValue = cell.StringCellValue; 
  302.                                 if (cellValue != null) 
  303.                                 { 
  304.                                     DataColumn column = new DataColumn(cellValue); 
  305.                                     data.Columns.Add(column); 
  306.                                 } 
  307.                             } 
  308.                         } 
  309.                         startRow = sheet.FirstRowNum + 1; 
  310.                     } 
  311.                     else 
  312.                     { 
  313.                         startRow = sheet.FirstRowNum; 
  314.                     } 
  315.  
  316.                     //最后一列的标号 
  317.                     int rowCount = sheet.LastRowNum; 
  318.                     for (int i = startRow; i <= rowCount; ++i) 
  319.                     { 
  320.                         IRow row = sheet.GetRow(i); 
  321.                         if (row == null) continue; //没有数据的行默认是null  
  322.  
  323.                         //处理空行数据 
  324.                         bool isBlankRow = true; 
  325.                         for (int j = row.FirstCellNum; j < cellCount; ++j) 
  326.                         { 
  327.                             if (row.GetCell(j) != null) //对象实例不为null 
  328.                             { 
  329.                                 if (!string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))//判断单元格是否为空 
  330.                                 { 
  331.                                     isBlankRow = false; 
  332.                                 } 
  333.  
  334.                             } 
  335.                         } 
  336.                         if (isBlankRow) 
  337.                         { 
  338.                             continue; 
  339.                         } 
  340.  
  341.                         //增加行数据 
  342.                         DataRow dataRow = data.NewRow(); 
  343.                         for (int j = row.FirstCellNum; j < cellCount; ++j) 
  344.                         { 
  345.                             if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null 
  346.                                 dataRow[j] = row.GetCell(j).ToString(); 
  347.                         } 
  348.                         data.Rows.Add(dataRow); 
  349.                     } 
  350.                 } 
  351.                 if (data != null) 
  352.                 { 
  353.                     DataView dv = data.DefaultView; 
  354.                     DataTable dtSort = dv.ToTable(); 
  355.                     ds.Tables.Add(dtSort); 
  356.                     return ds; 
  357.                 } 
  358.                 else 
  359.                 { 
  360.                     return null; 
  361.                 } 
  362.  
  363.             } 
  364.             catch (Exception ex) 
  365.             { 
  366.                 UMessageBox.Information(ex.Message); 
  367.                 return null; 
  368.             } 
  369.         } 
  370.         #endregion 
  371.  
  372.         #region 将DataTable数据导入到excel中 
  373.         /// <summary> 
  374.         /// 将DataTable数据导入到excel中 
  375.         /// </summary> 
  376.         /// <param name="data">要导入的数据</param> 
  377.         /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 
  378.         /// <param name="sheetName">要导入的excel的sheet的名称</param> 
  379.         /// <returns>导入数据行数(包含列名那一行)</returns> 
  380.         public int DataTableToExcelForDirectory(DataTable data, string sheetName, bool isColumnWritten, string fileName, DJType type, string description = "") 
  381.         { 
  382.             int i = 0; 
  383.             int j = 0; 
  384.             int count = 0; 
  385.             ISheet sheet = null; 
  386.             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 
  387.             if (fileName.IndexOf(".xlsx") > 0) // 2007版本 
  388.                 workbook = new XSSFWorkbook(); 
  389.             else if (fileName.IndexOf(".xls") > 0) // 2003版本 
  390.                 workbook = new HSSFWorkbook(); 
  391.             try 
  392.             { 
  393.                 if (workbook != null) 
  394.                 { 
  395.                     sheet = workbook.CreateSheet(sheetName); 
  396.                 } 
  397.                 else 
  398.                 { 
  399.                     return -1; 
  400.                 } 
  401.                 if (!string.IsNullOrEmpty(description))//添加描述信息 
  402.                 { 
  403.                     IRow row = sheet.CreateRow(count); 
  404.                     ICellStyle cellStyle = workbook.CreateCellStyle(); 
  405.                     ICell cell = row.CreateCell(0); 
  406.                     cell.SetCellValue(description); 
  407.                     cellStyle.WrapText = true;//自动换行 
  408.                     cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin; 
  409.                     cell.CellStyle = cellStyle; 
  410.                     sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(count, count, 0, data.Columns.Count - 1)); 
  411.                     row.Height = 30 * 20; 
  412.                     count++; 
  413.                 } 
  414.  
  415.                 if (isColumnWritten == true) //写入DataTable的列名 
  416.                 { 
  417.                     IRow row = sheet.CreateRow(count); 
  418.                     IDataFormat format = workbook.CreateDataFormat(); 
  419.                     for (j = 0; j < data.Columns.Count; ++j) 
  420.                     { 
  421.                         ICell cell = row.CreateCell(j); 
  422.                         ICellStyle cellStyle = workbook.CreateCellStyle(); 
  423.                         if (data.Columns[j].DataType.ToString() == "System.Decimal") 
  424.                         { 
  425.                             cellStyle.DataFormat = format.GetFormat("0.00"); 
  426.                         } 
  427.                         else 
  428.                         { 
  429.                             cellStyle.DataFormat = format.GetFormat("@"); 
  430.                         } 
  431.                         HSSFFont ffont = (HSSFFont)workbook.CreateFont(); 
  432.                         ffont.FontHeight = 16 * 16; 
  433.                         ffont.FontName = "宋体"; 
  434.                         cellStyle.SetFont(ffont); 
  435.                         cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin; 
  436.                         cell.CellStyle = cellStyle; 
  437.                         cell.SetCellValue(data.Columns[j].ColumnName); 
  438.                         row.Cells[j] = cell; 
  439.                         /*设置列宽*/ 
  440.                         sheet.SetColumnWidth(j, (data.Columns[j].ColumnName.Length) * 3 * 256); 
  441.  
  442.  
  443.                         switch (type) 
  444.                         { 
  445.                             case DJType.JXCZWPZInfo: 
  446.                                 if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类") 
  447.                                 { 
  448.                                     //设置生成下拉框的行和列 
  449.                                     var cellRegions = new CellRangeAddressList(count++, 65535, j, j); 
  450.                                     //设置 下拉框内容 
  451.                                     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( 
  452.                                         new string[] { "入库", "出库" }); 
  453.                                     //绑定下拉框和作用区域,并设置错误提示信息 
  454.                                     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); 
  455.                                     dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); 
  456.                                     dataValidate.ShowPromptBox = true; 
  457.                                     sheet.AddValidationData(dataValidate); 
  458.                                 } 
  459.                                 break; 
  460.                             case DJType.DBZWPZInfo: 
  461.                                 if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类") 
  462.                                 { 
  463.                                     //设置生成下拉框的行和列 
  464.                                     var cellRegions = new CellRangeAddressList(count++, 65535, j, j); 
  465.                                     //设置 下拉框内容 
  466.                                     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( 
  467.                                         new string[] { "调拨" }); 
  468.                                     //绑定下拉框和作用区域,并设置错误提示信息 
  469.                                     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); 
  470.                                     dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); 
  471.                                     dataValidate.ShowPromptBox = true; 
  472.                                     sheet.AddValidationData(dataValidate); 
  473.                                 } 
  474.                                 break; 
  475.                             case DJType.SRZWPZInfo: 
  476.                                 if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类") 
  477.                                 { 
  478.                                     //设置生成下拉框的行和列 
  479.                                     var cellRegions = new CellRangeAddressList(count++, 65535, j, j); 
  480.                                     //设置 下拉框内容 
  481.                                     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( 
  482.                                         new string[] { "门诊收入", "住院收入" }); 
  483.                                     //绑定下拉框和作用区域,并设置错误提示信息 
  484.                                     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); 
  485.                                     dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); 
  486.                                     dataValidate.ShowPromptBox = true; 
  487.                                     sheet.AddValidationData(dataValidate); 
  488.                                 } 
  489.                                 break; 
  490.                             case DJType.FYZWPZInfo: 
  491.                                 if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类") 
  492.                                 { 
  493.                                     //设置生成下拉框的行和列 
  494.                                     var cellRegions = new CellRangeAddressList(count++, 65535, j, j); 
  495.                                     //设置 下拉框内容 
  496.                                     DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( 
  497.                                         new string[] { "门诊收入", "住院收入" }); 
  498.                                     //绑定下拉框和作用区域,并设置错误提示信息 
  499.                                     HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); 
  500.                                     dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); 
  501.                                     dataValidate.ShowPromptBox = true; 
  502.                                     sheet.AddValidationData(dataValidate); 
  503.                                 } 
  504.                                 break; 
  505.                         } 
  506.                         if (data.Columns[j].ColumnName.Trim().ToUpper() == "推送状态") 
  507.                         { 
  508.                             //设置生成下拉框的行和列 
  509.                             var cellRegions = new CellRangeAddressList(count++, 65535, j, j); 
  510.                             //设置 下拉框内容 
  511.                             DVConstraint constraint = DVConstraint.CreateExplicitListConstraint( 
  512.                                 new string[] { "新增", "编辑", "删除" }); 
  513.                             //绑定下拉框和作用区域,并设置错误提示信息 
  514.                             HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); 
  515.                             dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); 
  516.                             dataValidate.ShowPromptBox = true; 
  517.                             sheet.AddValidationData(dataValidate); 
  518.                         } 
  519.                     } 
  520.                     count++; 
  521.                 } 
  522.  
  523.                 for (i = 0; i < data.Rows.Count; ++i) 
  524.                 { 
  525.                     IRow row = sheet.CreateRow(count); 
  526.                     for (j = 0; j < data.Columns.Count; ++j) 
  527.                     { 
  528.                         ICell cell = row.CreateCell(j); 
  529.                         cell.SetCellValue(data.Rows[i][j].ToString()); 
  530.                         ICellStyle cellStyle = workbook.CreateCellStyle(); 
  531.                         IDataFormat format = workbook.CreateDataFormat(); 
  532.                         cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin; 
  533.                         if (data.Columns[j].DataType.ToString() == "System.Decimal") 
  534.                         { 
  535.                             cellStyle.DataFormat = format.GetFormat("0.00"); 
  536.                         } 
  537.                         else 
  538.                         { 
  539.                             cellStyle.DataFormat = format.GetFormat("@"); 
  540.                         } 
  541.                         HSSFFont ffont = (HSSFFont)workbook.CreateFont(); 
  542.                         ffont.FontHeight = 14 * 14; 
  543.                         ffont.FontName = "宋体"; 
  544.                         cellStyle.SetFont(ffont); 
  545.                         cell.CellStyle = cellStyle; 
  546.                         cell.SetCellValue(data.Columns[j].ColumnName); 
  547.                         row.Cells[j] = cell; 
  548.                     } 
  549.                     ++count; 
  550.                 } 
  551.                 workbook.Write(fs); //写入到excel 
  552.                 fs.Close(); 
  553.                 return count; 
  554.             } 
  555.             catch (Exception ex) 
  556.             { 
  557.                 Console.WriteLine("Exception: " + ex.Message); 
  558.                 return -1; 
  559.             } 
  560.         } 
  561.         #endregion 
  562.  
  563.         #region 资源释放方法 
  564.         public void Dispose() 
  565.         { 
  566.             Dispose(true); 
  567.             GC.SuppressFinalize(this); 
  568.         } 
  569.  
  570.         protected virtual void Dispose(bool disposing) 
  571.         { 
  572.             if (!this.disposed) 
  573.             { 
  574.                 if (disposing) 
  575.                 { 
  576.                     if (fs != null) 
  577.                         fs.Close(); 
  578.                 } 
  579.  
  580.                 fs = null; 
  581.                 disposed = true; 
  582.             } 
  583.         } 
  584.         #endregion 
  585.  
  586.         #region 检查DataTable列是否存在 
  587.         /// <summary> 
  588.         /// 检查DataTable列是否存在 
  589.         /// </summary> 
  590.         /// <param name="dataTable"></param> 
  591.         /// <param name="arrayList"></param> 
  592.         public static bool CheckColumn(DataTable dataTable, ArrayList arrayList, out string message) 
  593.         { 
  594.             message = ""; 
  595.             foreach (var item in arrayList) 
  596.             { 
  597.                 if (!dataTable.Columns.Contains(item.ToString())) 
  598.                 { 
  599.                     message = "列 【" + item.ToString() + "】不存在,请检查!";  
  600.                     return false;  
  601.                 }  
  602.             }  
  603.             return true;  
  604.         }  
  605.         #endregion  
  606.  
  607.         #endregion  
  608.     }  
  609. }  
本文地址:https://www.lisen.me/npoi-sets-information-about-column-width-row-height-drop-down-list-and-so-on.html
版权声明:本文为原创文章,版权归 木子网 所有,欢迎分享本文,转载请保留出处!

发表评论


表情