1.ExcelHelper封装
namespace NPOI操作Excel
{
public class ExcelHelper
{
/// <summary>
/// DataTable转成Excel，返回一个文件流
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static Stream DatatableToExcel(DataTable dataTable)
{
IWorkbook wk = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = wk.CreateSheet();
IRow headerRow = sheet.CreateRow(); //处理标题部分
foreach (DataColumn column in dataTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//处理table中的数据
int rowIndex = ;
foreach (DataRow row in dataTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dataTable.Columns)
{
//column.Ordinal得到从0开始的列的位置，column.ColumnName的到列的名称
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column.ColumnName].ToString());
}
rowIndex++;
}
wk.Write(ms);
ms.Flush();
ms.Position = ; sheet = null;
headerRow = null;
return ms;
} /// <summary>
/// 读取Excel文件,转换成DataTable
/// </summary>
/// <param name="ExcelFileStream">读取到Excel的文件流</param>
/// <param name="SheetIndex">表的索引</param>
/// <param name="HeaderRowIndex">标题行的索引</param>
/// <returns></returns>
public static DataTable DataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
{
IWorkbook wk = new HSSFWorkbook(ExcelFileStream);
ISheet sheet = wk.GetSheetAt(SheetIndex); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum; //处理标题行的数据
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//循环遍历sheet中的每一行，读取每一单元格的数据，同时创建table中的每一行，填充数据
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
//以表的格式创建一个新的行
DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
//dataRow的数据填充好后把行加到表中
table.Rows.Add(dataRow);
}
ExcelFileStream.Close();
wk = null;
sheet = null;
return table;
}
}
}
2.SqlHelper封装
namespace NPOI操作Excel
{
public static class SqlHelper
{
//从配置文件中读取连接字符串
private static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
//执行命令的方法 insert update delete
public static int ExecuteNonquey(string sql, params SqlParameter[] ps)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.Parameters.AddRange(ps);
return cmd.ExecuteNonQuery();
}
}
}
//获取首行首列
public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.Parameters.AddRange(ps);
return cmd.ExecuteScalar();
}
}
} //获取读取数据库的对象
public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] ps)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
try
{
con.Open();
cmd.Parameters.AddRange(ps);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
//获取结果集
public static DataTable GetDataTable(string sql, params SqlParameter[] ps)
{
//构造数据表，用于接收查询结果
DataTable dt = new DataTable();
//构造适配器对象
using (SqlDataAdapter sda = new SqlDataAdapter(sql, conStr))
{
//添加参数
sda.SelectCommand.Parameters.AddRange(ps);
//执行
sda.Fill(dt);
return dt;
}
} }
}
3.winform中操作
3.1从数据库读取数据导入到Excel中
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = SqlHelper.GetDataTable("select * from t_persons");
MemoryStream ms = ExcelHelper.DatatableToExcel(dt) as MemoryStream; string saveFileNme = "";
bool fileSaved = false;
//保存文件对话框
SaveFileDialog saveDialog = new SaveFileDialog();
//设置默认的文件类型
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
//设置文件名
saveDialog.FileName = "保存";
saveDialog.ShowDialog();
//获得文件全路径
saveFileNme = saveDialog.FileName;
if (saveFileNme.IndexOf(":") < ) return;
if (saveFileNme != "")
{
try
{
FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create);
fs.Write(ms.GetBuffer(), , ms.GetBuffer().Length);
ms.Close();
ms.Dispose();
fs.Close();
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件出错，文件可能正在被占用\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
GC.Collect();//强行销毁
if (fileSaved&&File.Exists(saveFileNme))
{
MessageBox.Show("导出成功", "通知");
}
else
{
MessageBox.Show("导出失败", "通知");
}
}
3.2读取Excel文件，转换成DataTable绑定到DataGridView上
private void button2_Click(object sender, EventArgs e)
{
//打开文件对话框
OpenFileDialog fileDialog=new OpenFileDialog();
//指定要打开文件的格式
fileDialog.Filter = "Excel文件|*.xls";
//设置默认打开路径
fileDialog.InitialDirectory = @"C:\Users\LWP1398\Desktop";
if (fileDialog.ShowDialog()==DialogResult.OK)
{
string fileName = fileDialog.FileName;//得到文件全路径
using (FileStream fsRead=new FileStream(fileName,FileMode.Open,FileAccess.Read))
{
DataTable dt = ExcelHelper.DataTableFromExcel(fsRead, , );
dgv.DataSource = dt;
}
}
MessageBox.Show("ok");
}
}
NPOI目前主要还是操作xls文件，对操作xlsx文件支持得不是很好