C#使用Datatable导出Excel
程序员文章站
2023-12-16 13:13:28
本文实例为大家分享了c#使用datatable导出excel的具体代码,供大家参考,具体内容如下
using npoi.ss.usermodel;
using...
本文实例为大家分享了c#使用datatable导出excel的具体代码,供大家参考,具体内容如下
using npoi.ss.usermodel; using npoi.xssf.usermodel; using system; using system.collections.generic; using system.data; using system.io; using system.linq; using newtonsoft.json; namespace easyframe.common { /// <summary> ///title调用信息 /// </summary> public class charttitle { /// <summary> /// 调用名 /// </summary> public string callname { get; set; } /// <summary> /// 标头信息 /// </summary> public list<chartname> setnamelist { get; set; } } public class chartname { /// <summary> /// 一级标头 /// </summary> public string name { get; set; } /// <summary> /// 二级标头 /// </summary> public list<string> chartchildnamelist { get; set; } } public class excelhelper { // /// <summary> /// datatable导出excel /// </summary> /// <param name="dt">数据</param> /// <param name="list">表头</param> /// <param name="counts">总条数</param> /// <param name="filepath">保存地址</param> /// <returns></returns> public static memorystream writeexcel(datatable dt, list<charttitle> objlist, string filepath) { var list = objlist.firstordefault(a => a.callname == "ccc").setnamelist; if (!string.isnullorempty(filepath) && null != dt && dt.rows.count > 0) { xssfworkbook book = new xssfworkbook(); npoi.ss.usermodel.isheet sheet = book.createsheet(dt.tablename); icell cell = null; icellstyle style = book.createcellstyle(); ifont font = book.createfont();//创建字体样式 ifont fonts = book.createfont();//创建字体样式 ifont fontss = book.createfont();//创建字体样式 font.color = npoi.hssf.util.hssfcolor.red.index;//设置字体颜色 icellstyle styles = book.createcellstyle(); //红色 fonts.color = npoi.hssf.util.hssfcolor.green.index; icellstyle styless = book.createcellstyle(); //绿色 fontss.color = npoi.hssf.util.hssfcolor.blue.index; icellstyle stylesss = book.createcellstyle(); //蓝色 styless.setfont(fonts); styles.setfont(font); stylesss.setfont(fontss); style.alignment = horizontalalignment.center; npoi.ss.usermodel.irow row1 = sheet.createrow(0); npoi.ss.usermodel.irow row2 = sheet.createrow(1); var row = row1; int num = 0;//记录列 var d = true;//是否第一次加载到二级表单 #region 创建表头 //两行单列 for (int i = 0; i < list.count; i++) { if (!d) { row = row1; d = true; } if (list[i].chartchildnamelist == null) { cell = row.createcell(num); sheet.setcolumnwidth(num, 15 * 256); cell.setcellvalue(list[i].name); cell.cellstyle = style; sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 1, num, num)); num++; continue; } //两行多列 var count = list[i].chartchildnamelist.count; //列数 cell = row.createcell(num); cell.setcellvalue(list[i].name); cell.cellstyle = style; sheet.addmergedregion(new npoi.ss.util.cellrangeaddress(0, 0, num, count + num - 1)); if (d) { d = false; row = row2; } var t = 0; var rang = sheet.getrow(1); for (int j = 0; j < count; j++) { sheet.setcolumnwidth(num + t, 5 * 256); cell = row.createcell(num + t); cell.setcellvalue(list[i].chartchildnamelist[j]); cell.cellstyle = style; t++; } num = num + count; } #endregion 创建表头 #region 写入数据 int customs = 0;//记录列数 int clouns = 0;//记录行数 filepath = string.format(filepath, "xxx"); #region 导出操作 var data1 = jsonconvert.deserializeobject<list<类名>>(dt.rows[0][0].tostring()); foreach (var item in data1) { npoi.ss.usermodel.irow rows = sheet.createrow(clouns + 2); customs = 0; rows.createcell(customs).setcellvalue(convert.tostring(item.no)); customs++; rows.createcell(customs).setcellvalue(convert.tostring(item.lotteryopenno)); customs++; for (int i = 0; i < item.wan.length; i++) { if (item.lotteryopenno.split(',')[0] == item.wan[i].tostring()) { cell = rows.createcell(customs); cell.setcellvalue(item.wan[i]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.wan[i]); customs++; } } for (int j = 0; j < item.qian.length; j++) { if (item.lotteryopenno.split(',')[1] == item.qian[j].tostring()) { cell = rows.createcell(customs); cell.setcellvalue(item.qian[j]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.qian[j]); customs++; } } for (int k = 0; k < item.bai.length; k++) { if (item.lotteryopenno.split(',')[2] == item.bai[k].tostring()) { cell = rows.createcell(customs); cell.setcellvalue(item.bai[k]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.bai[k]); customs++; } } for (int l = 0; l < item.shi.length; l++) { if (item.lotteryopenno.split(',')[3] == (item.shi[l].tostring())) { cell = rows.createcell(customs); cell.setcellvalue(item.shi[l]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.shi[l]); customs++; } } for (int m = 0; m < item.ge.length; m++) { if (item.lotteryopenno.split(',')[4] == (item.ge[m].tostring())) { cell = rows.createcell(customs); cell.setcellvalue(item.ge[m]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.ge[m]); customs++; } } for (int n = 0; n < item.fen.length; n++) { if (item.lotteryopenno.contains(item.fen[n].tostring())) { cell = rows.createcell(customs); cell.setcellvalue(item.fen[n]); cell.cellstyle = styles; customs++; } else { rows.createcell(customs).setcellvalue(item.fen[n]); customs++; } } clouns++; } #endregion #endregion 写入数据 // 写入到客户端 using (system.io.memorystream ms = new system.io.memorystream()) { book.write(ms); using (filestream fs = new filestream(filepath, filemode.create, fileaccess.write)) { byte[] data = ms.toarray(); fs.write(data, 0, data.length); fs.flush(); } book = null; return ms; } } return null; } } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。