背景
我之前是做的Java开发的相关工作,c#开发还是第一次。
公司有这个需求,只好研究一下了。
我接手的是别人开发过两个报表的小玩意,万事开头难,刚开始为了搞清楚这些代码的意思走了不少弯路,下面说说我的理解,有不对的地方希望指出来,谢谢。
开始
我们先把准备工作做好,去官网下载NPOI,其他的我随后解释
NPOI官网下载地址:/packages/NPOI/
在这里可以看到最新的版本是NPOI2.4.1版本
下载压缩包——在这找到Download package,下载压缩包
添加引用
下载完之后,解压,打开解压文件夹下的release文件夹,你会看到有net20和net40文件夹,这里我们打开net40,可以看到一些dll文件,我们需要的就是引用NPOI.dll。
在这里我用的是Visual Studio community 版本,这个版本是学生、开发源代码参与者和个人免费使用的。
我们打开VS界面,添加引用,选择刚刚那个dll,并确定。添加完成后,可以在引用里面看到。至于其他的引用你们可以照着这个来,数据库用的是mysql数据库,其他的引用小编也不是都知道,哈。
useing一些必要的东东然后useing 这些玩意
using System;using System.Collections.Generic;using ponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using MySql.Data.MySqlClient;using System.IO;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;
然后就可以开始开发了,项目结构自己定,导出数据的界面可自由设计合适的
设计可视化界面
进到设计界面视图,选视图-——选择工具箱会出现这个页面,左侧是组件,鼠标拖动左侧组件到tabPage11就可自由设计你想要的页面了,在这我列举了几个例子,都是表头上用来筛选数据的,
界面的一些简单设置
除此之外,你需要把表格和表头加上去,标了步骤,首先找属性里的杂项,这一列最右边(集合)框里有三个点 ...
点击会出现一个编辑列的框,在这可设置表头文字,表头宽度,表头文字高度,数据来源就是数据库里的字段,尽量和查询的字段保持一致
可自由添加列,根据需要添加,这个就是NPOI查询到的数据的展示界面
表格上面设置有 查询 和 导出 按钮,用来查询数据和导出数据到ecxel表格
ok,设计完界面,下面可以进行实际开发工作了
查询代码
这段代码就是对应的查询代码,模糊查询就是表头上用来筛选数据的东东
就是这些,不多说上图
///应收/应付private void button1_Click(object sender, EventArgs e){//charset=gb2312;是编码格式,数据库用户名一般都是rootstring connStr = "server=服务器IP;user id=root;password=数据库密码;database=数据库名;charset=gb2312;";string query = "select g.tour_groupno,p.tour_companyName,g.tour_lineName,h.tour_name as tour_jdId,g.tour_countMan,g.tour_countChild," +"(CASE j.tour_type WHEN 0 THEN '云南线' WHEN 1 THEN '泰国线' WHEN 2 THEN '贵州' WHEN 3 THEN '四川' WHEN 4 THEN '海南' WHEN 5 THEN '西北'WHEN 6 THEN '桂林' ELSE 'null' END) as tour_type," +"p.tour_cwName,t.tour_date,q.tour_money,p.tour_money as tour_moneytot,h.tour_ysmoney,h.tour_alreadymoney,p.tour_mark,p.tour_cwStatus from " +"(select tour_id,tour_date from tour_payingsearch " +"where tour_date between '" + date1.Value.ToString("yyyy-MM-dd") + "' and '" + date2.Value.ToString("yyyy-MM-dd") + "' ";query += "and tour_cid = 4 and tour_cate=" + cb1.SelectedValue + " ) as t " +"inner join tour_paying p on p.tour_id=t.tour_id ";//模糊查询公司名称if (!string.IsNullOrEmpty(textBox2.Text))query += "and p.tour_companyName like '%" + textBox2.Text.Trim() + "%' ";query += "inner join tour_payinginfo q on p.tour_Id = q.tour_payingid " +"inner join tour_group g on g.tour_id = q.tour_groupId ";//模糊查询团号if (!string.IsNullOrEmpty(textBox1.Text))query += "and g.tour_groupno like '%" + textBox1.Text.Trim() + "%'";//模糊查询线路名称if (!string.IsNullOrEmpty(textBox6.Text))query += "and g.tour_lineName like '%" + textBox6.Text.Trim() + "%'";//模糊查询备注名称if (!string.IsNullOrEmpty(textBox22.Text))query += "and p.tour_mark like '%" + textBox22.Text.Trim() + "%' ";query += "inner join tour_groupcompanybill h on g.tour_id = h.tour_gid and q.tour_billId=h.tour_id ";//模糊查询工作人员名称if (!string.IsNullOrEmpty(textBox30.Text))query += "and h.tour_name like '%" + textBox30.Text.Trim() + "%' ";query += "inner join tour_groupsearch j on j.tour_Id = g.tour_id ";//类型模糊查询if (!string.IsNullOrEmpty(textBox24.Text))query += "and (CASE j.tour_type WHEN 0 THEN '云南' WHEN 1 THEN '泰国' WHEN 2 THEN '贵州' WHEN 3 THEN '四川' WHEN 4 THEN '海南' WHEN 5 THEN '西北'WHEN 6 THEN '桂林' ELSE 'null' END) like '%" + textBox24.Text.Trim() + "%' ";try{MySqlConnection conn = new MySqlConnection(connStr);conn.Open();MySqlCommand myCommand = new MySqlCommand(query, conn);myCommand.ExecuteNonQuery();MySqlDataReader myDataReader = myCommand.ExecuteReader();dt = ConvertDataReaderToDataTable(myDataReader);sumdt();gd1.DataSource = dt;}catch (Exception ex) { MessageBox.Show(ex.Message); }}
表格设置代码
此外,有一个重要的对datatable的设置,我理解为对表格的一些设置,看图
巴拉巴拉这一段,我也不是很清楚什么意思,总之,是对表格的设置就对了
private static DataTable ConvertDataReaderToDataTable(MySqlDataReader reader){try{DataTable objDataTable = new DataTable();int intFieldCount = reader.FieldCount;for (int intCounter = 0; intCounter < intFieldCount; ++intCounter){objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));}objDataTable.BeginLoadData();object[] objValues = new object[intFieldCount];while (reader.Read()){reader.GetValues(objValues);objDataTable.LoadDataRow(objValues, true);}reader.Close();objDataTable.EndLoadData();return objDataTable;}catch (Exception ex){throw new Exception("转换出错!"+ex.Message);}}
统计表格数据
此外有一个统计功能,就是对一列数据进行统计数量,这个里面有一些必要的设置
private void sumdt(){if (dt != null){int cnt = 0;decimal tot = 0.00M;//单团核销金额decimal ystot = 0.00M;//应收金额decimal atot = 0.00M;//已收金额decimal huizong = 0.00M;//汇总金额decimal countMan = 0M;//大人人数decimal countChild = 0M;//小孩人数foreach (DataRow dr in dt.Rows){cnt++;tot += Convert.ToDecimal(dr["tour_money"]);ystot += Convert.ToDecimal(dr["tour_ysmoney"]);atot += Convert.ToDecimal(dr["tour_alreadymoney"]);huizong += Convert.ToDecimal(dr["tour_moneytot"]);countMan += Convert.ToDecimal(dr["tour_countMan"]);countChild += Convert.ToDecimal(dr["tour_countChild"]);}DataRow dr1 = dt.NewRow();dr1["tour_groupno"] = "总计" + cnt + "条";dr1["tour_money"] = tot;dr1["tour_ysmoney"] = ystot;dr1["tour_alreadymoney"] = atot;dr1["tour_moneytot"] = huizong;dr1["tour_countMan"] = countMan;dr1["tour_countChild"] = countChild;dt.Rows.Add(dr1);}}
对表格的定义
这个dt!=null的dt是对表格的简称,不定义下面没法用,是在这设置的
导出数据
在这里我就贴几个字段的查询好了,要不然代码太多了
private void button2_Click(object sender, EventArgs e){if (dt == null) return;try{//设置导出格式,这里设置是.xlsSaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.Filter = "Execl files (*.xls)|*.xls";saveFileDialog.FilterIndex = 0;saveFileDialog.RestoreDirectory = true;saveFileDialog.CreatePrompt = true;saveFileDialog.Title = "Export Excel File";saveFileDialog.ShowDialog();if (saveFileDialog.FileName == "")return;FileStream file = new FileStream(saveFileDialog.FileName, FileMode.Create, FileAccess.ReadWrite);//创建workbookvar hssfworkbook = new HSSFWorkbook();IFont font = hssfworkbook.CreateFont();font.FontHeightInPoints = 10;IFont tfont = hssfworkbook.CreateFont();tfont.FontHeightInPoints = 14;tfont.Boldweight = (short)FontBoldWeight.BOLD;IFont bfont = hssfworkbook.CreateFont();bfont.FontHeightInPoints = 10;bfont.Boldweight = (short)FontBoldWeight.BOLD;//创建sheetHSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("明细");ICellStyle blackBorder = hssfworkbook.CreateCellStyle();blackBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;blackBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;blackBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;blackBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;blackBorder.SetFont(font);//表头ICellStyle moneystyle = hssfworkbook.CreateCellStyle();moneystyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;moneystyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;moneystyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;moneystyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;moneystyle.SetFont(font);IDataFormat format = hssfworkbook.CreateDataFormat();moneystyle.DataFormat = format.GetFormat("#,##0.00");ICellStyle intstyle = hssfworkbook.CreateCellStyle();intstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;intstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;intstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;intstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;intstyle.SetFont(font);IDataFormat format2 = hssfworkbook.CreateDataFormat();intstyle.DataFormat = format2.GetFormat("0");ICellStyle styleMiddle = hssfworkbook.CreateCellStyle();styleMiddle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;styleMiddle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;styleMiddle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;styleMiddle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;styleMiddle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;styleMiddle.VerticalAlignment = VerticalAlignment.CENTER;styleMiddle.SetFont(font);ICellStyle titlestyle = hssfworkbook.CreateCellStyle();titlestyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;titlestyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;titlestyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;titlestyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;titlestyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;titlestyle.VerticalAlignment = VerticalAlignment.CENTER;titlestyle.SetFont(tfont);//对表头的设置,导出的表头就是这里设置的int rowno = 0;var header = sheet1.CreateRow(rowno);var hcell = header.CreateCell(0);sheet1.SetColumnWidth(0, 24 * 256);hcell.SetCellValue("团号");hcell.CellStyle = titlestyle;hcell = header.CreateCell(1);sheet1.SetColumnWidth(1, 50 * 256);hcell.SetCellValue("公司名称");hcell.CellStyle = titlestyle;hcell = header.CreateCell(2);sheet1.SetColumnWidth(2, 12 * 256);hcell.SetCellValue("线路名称");hcell.CellStyle = titlestyle;hcell = header.CreateCell(3);sheet1.SetColumnWidth(3, 11 * 256);hcell.SetCellValue("类型");hcell.CellStyle = titlestyle;hcell = header.CreateCell(4);sheet1.SetColumnWidth(4, 11 * 256);hcell.SetCellValue("计调");hcell.CellStyle = titlestyle;hcell = header.CreateCell(5);sheet1.SetColumnWidth(5, 11 * 256);hcell.SetCellValue("财务");hcell.CellStyle = titlestyle;hcell = header.CreateCell(6);sheet1.SetColumnWidth(6, 14 * 256);hcell.SetCellValue("日期");hcell.CellStyle = titlestyle;hcell = header.CreateCell(7);sheet1.SetColumnWidth(7, 16 * 256);hcell.SetCellValue("大人人数");hcell.CellStyle = titlestyle;hcell = header.CreateCell(8);sheet1.SetColumnWidth(8, 16 * 256);hcell.SetCellValue("小孩人数");hcell.CellStyle = titlestyle;hcell = header.CreateCell(9);sheet1.SetColumnWidth(9, 16 * 256);hcell.SetCellValue("单团核销金额");hcell.CellStyle = titlestyle;hcell = header.CreateCell(10);sheet1.SetColumnWidth(10, 16 * 256);hcell.SetCellValue("汇总金额");hcell.CellStyle = titlestyle;hcell = header.CreateCell(11);sheet1.SetColumnWidth(11, 16 * 256);hcell.SetCellValue("应收金额");hcell.CellStyle = titlestyle;hcell = header.CreateCell(12);sheet1.SetColumnWidth(12, 16 * 256);hcell.SetCellValue("已收金额");hcell.CellStyle = titlestyle;hcell = header.CreateCell(13);sheet1.SetColumnWidth(13, 40 * 256);hcell.SetCellValue("备注");hcell.CellStyle = titlestyle;hcell = header.CreateCell(14);sheet1.SetColumnWidth(14, 8 * 256);hcell.SetCellValue("审核"); hcell.CellStyle = titlestyle;//表内数据就是这里foreach (DataRow dr in dt.Rows){rowno++;var bottom = sheet1.CreateRow(rowno);var botcell = bottom.CreateCell(0);botcell.SetCellValue(dr["tour_groupno"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(1);botcell.SetCellValue(dr["tour_companyName"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(2);botcell.SetCellValue(dr["tour_lineName"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(3);botcell.SetCellValue(dr["tour_type"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(4);//botcell.SetCellValue(dr["tour_jdId"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(5);botcell.SetCellValue(dr["tour_cwName"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(6);botcell.SetCellValue(dr["tour_date"]!=DBNull.Value?Convert.ToDateTime(dr["tour_date"]).ToString("yyyy-MM-dd"):"");botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(7);botcell.SetCellValue(dr["tour_countMan"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(8);botcell.SetCellValue(dr["tour_countChild"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(9);botcell.SetCellValue(Convert.ToDouble(dr["tour_money"]));botcell.CellStyle = moneystyle;botcell = bottom.CreateCell(10);botcell.SetCellValue(dr["tour_moneytot"] != DBNull.Value ? Convert.ToDouble(dr["tour_moneytot"]) : Convert.ToDouble(dr["tour_money"]));botcell.CellStyle = moneystyle;botcell = bottom.CreateCell(11);botcell.SetCellValue(Convert.ToDouble(dr["tour_ysmoney"]));botcell.CellStyle = moneystyle;botcell = bottom.CreateCell(12);botcell.SetCellValue(Convert.ToDouble(dr["tour_alreadymoney"]));botcell.CellStyle = moneystyle;botcell = bottom.CreateCell(13);botcell.SetCellValue(dr["tour_mark"].ToString());botcell.CellStyle = blackBorder;botcell = bottom.CreateCell(14);botcell.SetCellValue(dr["tour_cwStatus"].ToString());botcell.CellStyle = blackBorder;}//转化为字节数组hssfworkbook.Write(file);file.Close();}catch (Exception ex){MessageBox.Show("导出文件出错!" + ex.Message);}}
这边完成就可以去测试了,我这是完成了这个项目没问题的,可以正常查询数据,导出数据
如果在开发NPOI批量导出数据过程中有问题,可评论,我看到会回复的。
若实在不行,可评论问我要源码,今天就写到这里,希望能帮到各位。