<转>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 = ;
IRow headerRow = sheet.CreateRow();
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 = ;
int iCellIndex = ;
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 = ;
iRowIndex++;
} //自适应列宽度
for (int i = ; 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;
}

private void ExportExcel_NOPI(DataTable table_data)
{ Dictionary<string, string> enToZh = new Dictionary<string, string>();//dataTable中需要转换的值<需要将key即datatable中的值转换为中文值>
enToZh.Add("ID", "序号"); enToZh.Add("LOGINDAYS", "总天数"); table_data.Columns.RemoveAt();
table_data.Columns.RemoveAt();//先要移除不需要的列
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Excel"); ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
ICellStyle stringStyle = hssfworkbook.CreateCellStyle();
stringStyle.VerticalAlignment = VerticalAlignment.Center; //取得列宽
int columnCount = table_data.Columns.Count;
int[] arrColWidth = new int[columnCount];
int width = ;
foreach (DataColumn column in table_data.Columns)
{
arrColWidth[column.Ordinal] = width;
} int rowIndex = ;
string temp_col1 = "";
int col1_s = ; foreach (DataRow row in table_data.Rows)
{ #region 新建表，填充列头，样式
if (rowIndex == || rowIndex == )
{
if (rowIndex != )
{
sheet1 = hssfworkbook.CreateSheet();
} #region 列头及样式 IRow headerRow = sheet1.CreateRow();
//240,255,255
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center; headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//将标题颜色设置成灰色
headStyle.FillPattern = FillPattern.SolidForeground;//FillPattern 为单元格背景色的填充样式
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font); foreach (DataColumn column in table_data.Columns)
{ string columName = column.ColumnName;
if (enToZh.ContainsKey(columName))
{
headerRow.CreateCell(column.Ordinal).SetCellValue(enToZh[columName]);
}
else
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
sheet1.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + ) * );//设置列宽 } #endregion rowIndex = ;
}
#endregion #region 填充内容 int j = ;
IRow dataRow = sheet1.CreateRow(rowIndex);
foreach (DataColumn column in table_data.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.CellStyle = stringStyle;
if (j == )
{
newCell.SetCellValue(rowIndex);
}
else if (drValue == "" && j > )//如果是"1",将单元格颜色变色
{
ICellStyle tempStyle = hssfworkbook.CreateCellStyle();
tempStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
tempStyle.FillPattern = FillPattern.SolidForeground;
newCell.CellStyle = tempStyle;
}
else
{
newCell.SetCellValue(drValue);
}
break;
case "System.Int32"://字符串类型
newCell.SetCellValue(drValue);
newCell.CellStyle = stringStyle;
break;
case "System.Double":
if (drValue != "")
{
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
}
else
{
newCell.SetCellValue("");
}
newCell.CellStyle = cellStyle;
break;
} #region 单元格合并（这里只合并第一列） if (j == && temp_col1 != drValue)
{
if (temp_col1 != "")
{
sheet1.AddMergedRegion(new CellRangeAddress(col1_s, rowIndex - , , ));
}
temp_col1 = drValue;
col1_s = rowIndex;
} #endregion j++;
}
#endregion rowIndex++;
} //冻结窗口 锁定表头和第一列
sheet1.CreateFreezePane(, , , ); //输出 Context.Response.ContentType = "application/vnd.ms-excel";
Context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Excel.xls"));
Context.Response.Clear(); MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
file.WriteTo(Context.Response.OutputStream);
Context.Response.End();
}