Npoi导入导出Excel操作

矫情吗；* 2022-05-16 08:27 276阅读 0赞

原文地址为： [Npoi导入导出Excel操作][Npoi_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;
            }

转载请注明本文地址： [Npoi导入导出Excel操作][Npoi_Excel]

[Npoi_Excel]: http://www.itdaan.com/blog/2014/03/19/5400853d6f2ac2207b8024849d2612ad.html