Datatable导出Excel

Datatable导出Excel

//Datatable导出Excel private static void GridToExcelByNPOI(DataTable dt, string strExcelFileName)         {             try             { HSSFWorkbook workbook = new HSSFWorkbook();                  ISheet sheet = workbook.CreateSheet("Sheet1");                  ICellStyle HeadercellStyle = workbook.CreateCellStyle();                 HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;                 HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;                 HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;                 HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                 HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;                 //字体                 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();                 headerfont.Boldweight = (short)FontBoldWeight.Bold;                 HeadercellStyle.SetFont(headerfont);                   //用column name 作为列名                 int icolIndex = 0;                 IRow headerRow = sheet.CreateRow(0);                 foreach (DataColumn item in dt.Columns)                 {                     ICell cell = headerRow.CreateCell(icolIndex);                     cell.SetCellValue(item.ColumnName);                     cell.CellStyle = HeadercellStyle;                     icolIndex++;                 }                  ICellStyle cellStyle = workbook.CreateCellStyle();                  //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看                 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");                 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;                 cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;                 cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;                 cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;                   NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();                 cellfont.Boldweight = (short)FontBoldWeight.Normal;                 cellStyle.SetFont(cellfont);                  //建立内容行                 int iRowIndex = 1;                 int iCellIndex = 0;                 foreach (DataRow Rowitem in dt.Rows)                 {                     IRow DataRow = sheet.CreateRow(iRowIndex);                     foreach (DataColumn Colitem in dt.Columns)                     {                          ICell cell = DataRow.CreateCell(iCellIndex);                         cell.SetCellValue(Rowitem[Colitem].ToString());                         cell.CellStyle = cellStyle;                         iCellIndex++;                     }                     iCellIndex = 0;                     iRowIndex++;                 }                  //自适应列宽度                 for (int i = 0; i < icolIndex; i++)                 {                     sheet.AutoSizeColumn(i);                 }                  //写Excel                 FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);                 workbook.Write(file);                 file.Flush();                 file.Close();                  MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);             }             catch (Exception ex)             {                 ILog log = LogManager.GetLogger("Exception Log");                 log.Error(ex.Message + Environment.NewLine + ex.StackTrace);                 //记录AuditTrail                 CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);                  MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);             }             finally { workbook = null; }          }

 

/// <summary>         /// Excel文件导成Datatable         /// </summary>         /// <param name="strFilePath">Excel文件目录地址</param>         /// <param name="strTableName">Datatable表名</param>         /// <param name="iSheetIndex">Excel sheet index</param>         /// <returns></returns>         public static DataTable XlSToDataTable(string strFilePath, string strTableName,int iSheetIndex)         {              string strExtName = Path.GetExtension(strFilePath);              DataTable dt = new DataTable();             if (!string.IsNullOrEmpty(strTableName))             {                 dt.TableName = strTableName;             }              if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))             {                 using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))                 {                     HSSFWorkbook workbook = new HSSFWorkbook(file);                     ISheet sheet = workbook.GetSheetAt(iSheetIndex);                      //列头                     foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)                     {                         dt.Columns.Add(item.ToString(),typeof(string));                     }                      //写入内容                     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();                     while(rows.MoveNext())                     {                         IRow row = (HSSFRow)rows.Current;                         if (row.RowNum == sheet.FirstRowNum)                         {                             continue;                         }                          DataRow dr = dt.NewRow();                         foreach (ICell item in row.Cells)                         {                             switch (item.CellType)                             {                                 case CellType.Boolean:                                     dr[item.ColumnIndex] = item.BooleanCellValue;                                     break;                                 case CellType.Error:                                     dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);                                     break;                                 case CellType.Formula:                                     switch (item.CachedFormulaResultType)                                     {                                         case CellType.Boolean:                                             dr[item.ColumnIndex] = item.BooleanCellValue;                                             break;                                         case CellType.Error:                                             dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);                                             break;                                         case CellType.Numeric:                                             if (DateUtil.IsCellDateFormatted(item))                                             {                                                 dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");                                             }                                             else                                             {                                                 dr[item.ColumnIndex] = item.NumericCellValue;                                             }                                             break;                                         case CellType.String:                                             string str = item.StringCellValue;                                             if (!string.IsNullOrEmpty(str))                                             {                                                 dr[item.ColumnIndex] = str.ToString();                                             }                                             else                                             {                                                 dr[item.ColumnIndex] = null;                                             }                                             break;                                         case CellType.Unknown:                                         case CellType.Blank:                                         default:                                             dr[item.ColumnIndex] = string.Empty;                                             break;                                     }                                     break;                                 case CellType.Numeric:                                     if (DateUtil.IsCellDateFormatted(item))                                     {                                         dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");                                     }                                     else                                     {                                         dr[item.ColumnIndex] = item.NumericCellValue;                                     }                                     break;                                 case CellType.String:                                     string strValue = item.StringCellValue;                                     if (string.IsNullOrEmpty(strValue))                                     {                                         dr[item.ColumnIndex] = strValue.ToString();                                     }                                     else                                     {                                         dr[item.ColumnIndex] = null;                                     }                                     break;                                 case CellType.Unknown:                                 case CellType.Blank:                                 default:                                     dr[item.ColumnIndex] = string.Empty;                                     break;                             }                         }                         dt.Rows.Add(dr);                     }                 }            }              return dt;         }

posted on 2016-03-18 10:46  笨小孩做开发 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/dullbaby/p/5291176.html