最近使用NPOI导出Datagridview数据。。。。。。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using DLZY;
using NPOI;
using NPOI.POIFS;
using NPOI.HSSF;
using NPOI.HPSF;
using NPOI.Util;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using System.IO;
using NPOI.SS.UserModel;
using Model;
using DAL;
namespace 收入查询
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
}
private void Bind()
{
SqlConnection conn = SqlHelper.Conn(); //连接数据库
//string sql = string.Format("select * from Income_Note where IncomeType ='{0}' and IncomeTime between '{1}' and '{2}'", srtype.Text, Convert.ToDateTime(kstime.Text), Convert.ToDateTime(jstime.Text));
string sql = "select * from Income_Note where IncomeType =@IncomeType and IncomeTime between @Start and @End ";
SqlDataAdapter ad = new SqlDataAdapter(sql, conn); //提交查询命令
ad.SelectCommand.Parameters.AddWithValue("@IncomeType", srtype.Text);
ad.SelectCommand.Parameters.AddWithValue("@Start", Convert.ToDateTime(kstime.Text));
ad.SelectCommand.Parameters.AddWithValue("@End", Convert.ToDateTime(jstime.Text));
DataTable dt = new DataTable(); //新建表
ad.Fill(dt);
srdata.DataSource = dt;// 显示收入查询返回的dt表结果到。。。
}
private void button1_Click(object sender, EventArgs e)
{
if (srtype.Text.Trim() != string.Empty)
{
Bind();
}
else
{
MessageBox.Show("信息有误,请输入正确的的值!");
}
}
private void button3_Click(object sender, EventArgs e)
{
Form3 frm3= new Form3();
frm3.ShowDialog();
}
private void button4_Click(object sender, EventArgs e)
{
if (srdata.SelectedRows.Count == 0)
{
MessageBox.Show("没有选择有效行");
return;
}
else if (MessageBox.Show("你确定要删除吗?", "警告", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
{
SqlConnection conn = SqlHelper.Conn();
conn.Open();
string sql = "delete from Income_Note where IncomeId=@id";
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddWithValue("id", srdata.SelectedRows[0].Cells["colid"].Value.ToString());
comm.ExecuteNonQuery();
conn.Close();
Bind();
MessageBox.Show("删除成功");
}
else
{
return;
}
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnExport_Click(object sender, EventArgs e)
{
//ExportTOExcel(srdata);
SaveFileDialog sdfexport = new SaveFileDialog();
sdfexport.Filter = "Excel文件|*.xls";
if (sdfexport.ShowDialog() == DialogResult.No)
{
return;
}
string filename = sdfexport.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("支出数据");
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("ID");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("用户姓名");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("收入方式");
rowHeader.CreateCell(3, CellType.STRING).SetCellValue("收入时间");
rowHeader.CreateCell(4, CellType.STRING).SetCellValue("收入金额");
rowHeader.CreateCell(5, CellType.STRING).SetCellValue("备注");
sheet.SetColumnWidth(3, 20 * 256);
string sql = "select * from Income_Note where IncomeType =@IncomeType and IncomeTime between @Start and @End ";
List<SqlParameter> paramsList = new List<SqlParameter>();
paramsList.Add(new SqlParameter("@IncomeType", srtype.Text));
paramsList.Add(new SqlParameter("@Start", Convert.ToDateTime(kstime.Text)));
paramsList.Add(new SqlParameter("@End", Convert.ToDateTime(jstime.Text)));
Income_Note[] incomenotes = new IncomeNoteDAL().Search(sql, paramsList);
for (int i = 0; i < incomenotes.Length; i++)
{
Income_Note incomenote = incomenotes[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0, CellType.NUMERIC).SetCellValue(incomenote.IncomeId);
row.CreateCell(1, CellType.STRING).SetCellValue(incomenote.UserName);
row.CreateCell(2, CellType.STRING).SetCellValue(incomenote.IncomeType);
ICellStyle styledate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
ICell cellDate = row.CreateCell(3, CellType.NUMERIC);
cellDate.CellStyle = styledate;
cellDate.SetCellValue(incomenote.IncomeTime);
HSSFCellStyle cellStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format2 = (HSSFDataFormat)workbook.CreateDataFormat();
//cellStyle2.DataFormat = format2.GetFormat("¥#,##0");
cellStyle2.DataFormat = format2.GetFormat("¥#,##0;¥-#,##0");
HSSFCell cellMoney = (HSSFCell)row.CreateCell(4, CellType.NUMERIC);
cellMoney.CellStyle = cellStyle2;
cellMoney.SetCellValue(incomenote.IncomeMoney.ToString());
row.CreateCell(5, CellType.STRING).SetCellValue(incomenote.IncomeNote);
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
}
}
截图如下所示:
导出xsl如下: