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操作