NOPI导入导出
2016-02-16 18:07 祥_venux 阅读(315) 评论(0) 编辑 收藏 举报using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
namespace Excel导出示例
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnIn_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "xls|*.xls|xlsx|*.xlsx";
ofd.ShowDialog();
string file = ofd.FileName;
Import(file);
}
private void btnOut_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.ShowDialog();
string file = sfd.FileName;
Outport(this.dataGridView1.DataSource as DataTable, file);
}
#region 导入
private void Import(string filePath)
{
try
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();//跳过第一行
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
this.dataGridView1.DataSource = dt;
this.dataGridView1.Refresh();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
///// <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 hssfworkbook = new HSSFWorkbook(file);
// ISheet sheet = hssfworkbook.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;
//}
#endregion
#region 导出
//Datatable导出Excel
private void Outport(DataTable dt, string filename)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
try
{
ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = hssfworkbook.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 = hssfworkbook.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 = hssfworkbook.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 = hssfworkbook.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(filename, FileMode.OpenOrCreate);
hssfworkbook.Write(file);
file.Flush();
file.Close();
MessageBox.Show("导出成功！");
}
catch (Exception ex)
{
MessageBox.Show("导出失败！");
}
finally
{
hssfworkbook = null;
}
}
#endregion
}
}
参考文章：
1.http://www.cnblogs.com/colder/p/3611906.html.
2.http://www.cnblogs.com/zhuawang/archive/2012/12/12/2815367.html.
