书接上文，上篇文章我们成功的下载了excel.
这回展示数据库中的数据。
1、获取数据源，获取一个DataTable
举个例子,获取数据库中的数据：
private static DataTable GetESIDPicture(int F_ESID)
{
string sql = "SELECT F_URL FROM TC_ESign WHERE F_ESID ='" + F_ESID + "'";
DataTable allDelegation = SqlHelper.GetDataTable(sql);
return allDelegation;
}调用上面方法：
DataTable rc=GetESIDPicture(2);rc 就是DataTable 数据了。这个是基础。循环输出下面的数据
创建excel在后面追加如下数据：
int i = 0, j = 3;
while (i < rc)
{
IRow row = sheet.CreateRow(j);
int k = 0;
int len = contentData.Rows[i].Table.Columns.Count;
while (k < len)
{
row.CreateCell(k).SetCellValue(contentData.Rows[i][k].ToString());
sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
++k;
}
++i;
++j;
}解释：Columns.Count为输出几列,默认全输出。
完整项目案例：
private string ExcelOnlineDown(AddRegFillFileLogExcelInsert addRegFillFileLog,DataSet ds)
{
string filepath = "";
DataTable contentData = ds.Tables[0];
DataTable d = GetESIDPicture(1);
DataTable contentDataTY = ds.Tables[1];
DataTable contentDataJL = ds.Tables[2];
int rc = contentData.Rows.Count;
int ty = contentDataTY.Rows.Count;
int jl = contentDataJL.Rows.Count;
if (rc < 1 && ty < 1 && jl < 1) return filepath;
IWorkbook workbook = new XSSFWorkbook();
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//style.BorderDiagonal
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 11;
ICellStyle style2 = workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//style.BorderDiagonal
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IFont font2 = workbook.CreateFont();
font2.FontHeightInPoints = 18;
//font2.Boldweight = (short)FontBoldWeight.Bold;//short.MaxValue;
style2.SetFont(font2);
ICellStyle ConStyle = workbook.CreateCellStyle();
ConStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ConStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ConStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ConStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
#region 填表说明
//创建工作表
ISheet sheetx = workbook.CreateSheet("填表说明");
IRow rowx0 = sheetx.CreateRow(0);
IRow rowx1 = sheetx.CreateRow(1);
IRow rowx2 = sheetx.CreateRow(2);
IRow rowx3 = sheetx.CreateRow(3);
IRow rowx4 = sheetx.CreateRow(4);
IRow rowx5 = sheetx.CreateRow(5);
IRow rowx6 = sheetx.CreateRow(6);
IRow rowx7 = sheetx.CreateRow(7);
IRow rowx8 = sheetx.CreateRow(8);
IRow rowx9 = sheetx.CreateRow(9);
sheetx.DefaultRowHeight = 200 * 2;
rowx0.Height = 30 * 20;
rowx1.Height = 30 * 20;
rowx2.Height = 30 * 20;
rowx3.Height = 30 * 20;
rowx4.Height = 30 * 20;
rowx5.Height = 30 * 20;
rowx6.Height = 30 * 20;
rowx7.Height = 30 * 20;
rowx8.Height = 30 * 20;
rowx9.Height = 30 * 20;
rowx0.CreateCell(0).SetCellValue("填 表 说 明");
rowx0.GetCell(0).CellStyle = style2;
rowx1.CreateCell(0).SetCellValue("1.运动员注册确认总表应按照注册类型顺序填写：首次注册、年度确认、交流注册、重新注册。");
rowx1.GetCell(0).CellStyle = style2;
rowx2.CreateCell(0).SetCellValue("2.首次注册运动员按姓名拼音的首个英文字母顺序排序。");
rowx2.GetCell(0).CellStyle = style2;
rowx3.CreateCell(0).SetCellValue("3.运动员姓名拼音格式：全部为小写字母，名字中每个字的拼音要分开，例如：张三 zhang san。");
rowx3.GetCell(0).CellStyle = style2;
rowx4.CreateCell(0).SetCellValue("4.有曾用名必须在表格中填写。");
rowx4.GetCell(0).CellStyle = style2;
rowx5.CreateCell(0).SetCellValue("5.出生日期、协议起止日期、协议终止日期等所有日期格式：XXXX-XX-XX，例如：2006-01-01，并设置为文本格式。");
rowx5.GetCell(0).CellStyle = style2;
rowx6.CreateCell(0).SetCellValue("6.运动员所属单位、交流单位，简写代表队的省或市，例如：北京。");
rowx6.GetCell(0).CellStyle = style2;
rowx7.CreateCell(0).SetCellValue("7.在打印各项表格时应调整好格式、字体，每页尽可能多印一些信息，节约用纸减轻留档压力。");
rowx7.GetCell(0).CellStyle = style2;
CellRangeAddress regio1 = new CellRangeAddress(0, 0, 0, 18);
CellRangeAddress regio2 = new CellRangeAddress(1, 1, 0, 18);
CellRangeAddress regio3 = new CellRangeAddress(2, 2, 0, 18);
CellRangeAddress regio4 = new CellRangeAddress(3, 3, 0, 18);
CellRangeAddress regio5 = new CellRangeAddress(4, 4, 0, 18);
CellRangeAddress regio6 = new CellRangeAddress(5, 5, 0, 18);
CellRangeAddress regio7 = new CellRangeAddress(6, 6, 0, 18);
CellRangeAddress regio8 = new CellRangeAddress(7, 7, 0, 18);
sheetx.AddMergedRegion(regio1);
sheetx.AddMergedRegion(regio2);
sheetx.AddMergedRegion(regio3);
sheetx.AddMergedRegion(regio4);
sheetx.AddMergedRegion(regio5);
sheetx.AddMergedRegion(regio6);
sheetx.AddMergedRegion(regio7);
sheetx.AddMergedRegion(regio8);
#endregion
string data = DateTime.Now.ToLongDateString().ToString();
string writeName = addRegFillFileLog.FillerName;
string phoneNumer = addRegFillFileLog.FillerPhone;
string filesign = "XX";
string delegationName = "";
int i = 0, j = 3;
var fileName = "";
var filePath = "";
var filePaths = "";
if (rc>0)
{
delegationName = contentData.Rows[0][7].ToString();
delegationJLName = delegationName;
fileName = delegationName + DateTime.Now.Year.ToString() + "羽毛球运动员注册确认总表";
filePath = System.Web.Hosting.HostingEnvironment.MapPath("/") + "/Excels/" + fileName + ".xlsx";
filePaths = "/Excels/" + fileName + ".xlsx";
#region 总表
//创建工作表
ISheet sheet = workbook.CreateSheet("1.运动员注册确认总表");
IRow row0 = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
IRow rowN = sheet.CreateRow(rc + 5);
row0.Height = 30 * 20;
sheet.DefaultRowHeight = 200 * 2;
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 12 * 256);
sheet.SetColumnWidth(3, 12 * 256);
sheet.SetColumnWidth(4, 12 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 18 * 256);
sheet.SetColumnWidth(7, 12 * 256);
sheet.SetColumnWidth(8, 15 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 20 * 256);
row0.CreateCell(0).SetCellValue("" + delegationName + "省、自治区、直辖市2020年度羽毛球运动员注册确认总表");
row1.CreateCell(0).SetCellValue("序号");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(7).SetCellValue("运动员");
row1.CreateCell(8).SetCellValue("协议起");
row1.CreateCell(9).SetCellValue("协议终");
row2.CreateCell(3).SetCellValue("曾用名");
row2.CreateCell(1).SetCellValue("（中）");
row2.CreateCell(2).SetCellValue("（英）");
row2.CreateCell(4).SetCellValue("性别");
row2.CreateCell(5).SetCellValue("出生日期");
row2.CreateCell(6).SetCellValue("身份证号");
row2.CreateCell(7).SetCellValue("所属单位");
row2.CreateCell(8).SetCellValue("始日期");
row2.CreateCell(9).SetCellValue("止日期");
row2.CreateCell(10).SetCellValue("注册类型");
rowN.CreateCell(0).SetCellValue("填表日期：" + data + " 填表人姓名：" + writeName + " 联系电话：" + phoneNumer + " 负责人签字： ");
rowN.CreateCell(7).SetCellValue("" + delegationName + "省、自治区、直辖市体育局公章：" + filesign + "");
row0.GetCell(0).CellStyle = style2;
row1.GetCell(0).CellStyle = style;
row1.GetCell(1).CellStyle = style;
row1.GetCell(2).CellStyle = style;
row1.GetCell(7).CellStyle = style;
row1.GetCell(8).CellStyle = style;
row1.GetCell(9).CellStyle = style;
row2.GetCell(1).CellStyle = style;
row2.GetCell(2).CellStyle = style;
row2.GetCell(3).CellStyle = style;
row2.GetCell(4).CellStyle = style;
row2.GetCell(5).CellStyle = style;
row2.GetCell(6).CellStyle = style;
row2.GetCell(7).CellStyle = style;
row2.GetCell(8).CellStyle = style;
row2.GetCell(9).CellStyle = style;
row2.GetCell(10).CellStyle = style;
//rowN.GetCell(0).CellStyle = style;
//rowN.GetCell(9).CellStyle = style;
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 10);
CellRangeAddress region2 = new CellRangeAddress(1, 2, 0, 0);
CellRangeAddress region4 = new CellRangeAddress(1, 1, 2, 2);
CellRangeAddress region5 = new CellRangeAddress(1, 1, 3, 3);
CellRangeAddress region6 = new CellRangeAddress(1, 1, 4, 4);
CellRangeAddress regionN = new CellRangeAddress(rc + 5, rc + 5 + 1, 0, 5);
CellRangeAddress regionP = new CellRangeAddress(rc + 5, rc + 5 + 1, 7, 10);
sheet.AddMergedRegion(region1);
sheet.AddMergedRegion(region2);
sheet.AddMergedRegion(region4);
sheet.AddMergedRegion(region5);
sheet.AddMergedRegion(region6);
sheet.AddMergedRegion(regionN);
sheet.AddMergedRegion(regionP);
while (i < rc)
{
IRow row = sheet.CreateRow(j);
int k = 0;
int len = contentData.Rows[i].Table.Columns.Count;
while (k < len)
{
row.CreateCell(k).SetCellValue(contentData.Rows[i][k].ToString());
sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
++k;
}
++i;
++j;
}
Picture(workbook, sheet, 6, rc + 5, 7, rc + 7, addRegFillFileLog.ESID);
}
#endregion
#region 退役表
if (ty >= 1)
{
//创建工作表
//string delegationName_TY = contentDataTY.Rows[0][2].ToString();
#region style
ISheet sheet2 = workbook.CreateSheet("2.运动员退役表");
IRow row0t = sheet2.CreateRow(0);
IRow row1t = sheet2.CreateRow(1);
IRow row2t = sheet2.CreateRow(2);
IRow rowNt = sheet2.CreateRow(ty + 5);
sheet2.DefaultRowHeight = 200 * 2;
row0t.Height = 30 * 20;
sheet2.SetColumnWidth(0, 20 * 256);
sheet2.SetColumnWidth(1, 20 * 256);
sheet2.SetColumnWidth(2, 20 * 256);
sheet2.SetColumnWidth(3, 20 * 256);
sheet2.SetColumnWidth(4, 20 * 256);
sheet2.SetColumnWidth(5, 20 * 256);
sheet2.SetColumnWidth(6, 20 * 256);
row0t.CreateCell(0).SetCellValue( "省、自治区、直辖市2020年度羽毛球注册运动员退役表");
row1t.CreateCell(0).SetCellValue("序号");
row1t.CreateCell(1).SetCellValue("姓名");
row1t.CreateCell(2).SetCellValue("性别");
row1t.CreateCell(3).SetCellValue("出生年月日");
row1t.CreateCell(4).SetCellValue("是否放弃注册优先权");
row1t.CreateCell(5).SetCellValue("去向");
row1t.CreateCell(6).SetCellValue("备注");
rowNt.CreateCell(0).SetCellValue("填表日期：" + data + " 填表人姓名：" + writeName + " 联系电话：" + phoneNumer + " 负责人签字：");
rowNt.CreateCell(5).SetCellValue("" + delegationJLName + "省、自治区、直辖市体育局公章：" + filesign + "");
row0t.GetCell(0).CellStyle = style2;
row1t.GetCell(0).CellStyle = style;
row1t.GetCell(1).CellStyle = style;
row1t.GetCell(2).CellStyle = style;
row1t.GetCell(3).CellStyle = style;
row1t.GetCell(4).CellStyle = style;
row1t.GetCell(5).CellStyle = style;
row1t.GetCell(6).CellStyle = style;
//rowNt.GetCell(0).CellStyle = style;
//rowNt.GetCell(5).CellStyle = style;
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region1t = new CellRangeAddress(0, 0, 0, 6);
CellRangeAddress region2t = new CellRangeAddress(1, 1, 0, 0);
CellRangeAddress region3t = new CellRangeAddress(1, 1, 2, 2);
CellRangeAddress region4t = new CellRangeAddress(1, 1, 3, 3);
CellRangeAddress region5t = new CellRangeAddress(1, 1, 4, 4);
CellRangeAddress regionNt = new CellRangeAddress(ty + 5, ty + 5 + 1, 0, 3);
CellRangeAddress regionNtP = new CellRangeAddress(ty + 5, ty + 5 + 1, 5, 6);
sheet2.AddMergedRegion(region1t);
sheet2.AddMergedRegion(region2t);
sheet2.AddMergedRegion(region3t);
sheet2.AddMergedRegion(region4t);
sheet2.AddMergedRegion(region5t);
sheet2.AddMergedRegion(regionNt);
sheet2.AddMergedRegion(regionNtP);
#endregion
i = 0; j = 2;
while (i < ty)
{
IRow row = sheet2.CreateRow(j);
int k = 0;
int len = contentDataTY.Rows[i].Table.Columns.Count;
while (k < len)
{
row.CreateCell(k).SetCellValue(contentDataTY.Rows[i][k].ToString());
sheet2.GetRow(j).GetCell(k).CellStyle = ConStyle;
++k;
}
++i;
++j;
}
Picture(workbook, sheet2, 4, ty + 5, 5, ty + 7, addRegFillFileLog.ESID);
}
#endregion
#region 交流表
if (jl >= 1)
{
//创建工作表
ISheet sheet3 = workbook.CreateSheet("3.运动员交流表");
string delegationName_JL = contentDataJL.Rows[0][6].ToString()==""? delegationJLName: contentDataJL.Rows[0][6].ToString();
#region style
sheet3.DefaultRowHeight = 200 * 2;
IRow row0f = sheet3.CreateRow(0);
IRow row1f = sheet3.CreateRow(1);
IRow row2f = sheet3.CreateRow(2);
IRow rowNf = sheet3.CreateRow(jl + 5);
row0f.Height = 30 * 20;
sheet3.SetColumnWidth(0, 15 * 256);
sheet3.SetColumnWidth(1, 15 * 256);
sheet3.SetColumnWidth(2, 15 * 256);
sheet3.SetColumnWidth(3, 15 * 256);
sheet3.SetColumnWidth(4, 15 * 256);
sheet3.SetColumnWidth(5, 15 * 256);
sheet3.SetColumnWidth(6, 15 * 256);
sheet3.SetColumnWidth(7, 15 * 256);
sheet3.SetColumnWidth(8, 15 * 256);
sheet3.SetColumnWidth(9, 15 * 256);
sheet3.SetColumnWidth(10, 15 * 256);
row0f.CreateCell(0).SetCellValue("" + delegationName_JL + "省、自治区、直辖市2020年度羽毛球运动员交流注册表");
row1f.CreateCell(0).SetCellValue("序号");
row1f.CreateCell(1).SetCellValue("姓名");
row1f.CreateCell(6).SetCellValue("运动员");
row1f.CreateCell(7).SetCellValue("运动员");
row1f.CreateCell(8).SetCellValue("协议起");
row1f.CreateCell(9).SetCellValue("协议终");
row2f.CreateCell(2).SetCellValue("曾用名");
row2f.CreateCell(1).SetCellValue("（中）");
row2f.CreateCell(3).SetCellValue("性别");
row2f.CreateCell(4).SetCellValue("出生日期");
row2f.CreateCell(5).SetCellValue("身份证号");
row2f.CreateCell(6).SetCellValue("原所属单位");
row2f.CreateCell(7).SetCellValue("交流单位");
row2f.CreateCell(8).SetCellValue("始日期");
row2f.CreateCell(9).SetCellValue("止日期");
row2f.CreateCell(10).SetCellValue("备注");
rowNf.CreateCell(0).SetCellValue("填表日期：" + data + " 填表人姓名：" + writeName + " 联系电话：" + phoneNumer + " 负责人签字：");
rowNf.CreateCell(6).SetCellValue("" + delegationName_JL + "省、自治区、直辖市体育局公章：" + filesign + "");
row0f.GetCell(0).CellStyle = style2;
row1f.GetCell(0).CellStyle = style;
row1f.GetCell(1).CellStyle = style;
row1f.GetCell(6).CellStyle = style;
row1f.GetCell(7).CellStyle = style;
row1f.GetCell(8).CellStyle = style;
row1f.GetCell(9).CellStyle = style;
row2f.GetCell(1).CellStyle = style;
row2f.GetCell(2).CellStyle = style;
row2f.GetCell(3).CellStyle = style;
row2f.GetCell(4).CellStyle = style;
row2f.GetCell(5).CellStyle = style;
row2f.GetCell(6).CellStyle = style;
row2f.GetCell(7).CellStyle = style;
row2f.GetCell(8).CellStyle = style;
row2f.GetCell(9).CellStyle = style;
row2f.GetCell(10).CellStyle = style;
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region1f = new CellRangeAddress(0, 0, 0, 10);
CellRangeAddress region2f = new CellRangeAddress(1, 2, 0, 0);
CellRangeAddress region3f = new CellRangeAddress(1, 1, 2, 2);
CellRangeAddress region4f = new CellRangeAddress(1, 1, 3, 3);
CellRangeAddress region5f = new CellRangeAddress(1, 1, 4, 4);
CellRangeAddress regionNf = new CellRangeAddress(jl + 5, jl + 5 + 1, 0, 4);
CellRangeAddress regionNfP = new CellRangeAddress(jl + 5, jl + 5 + 1, 6, 9);
sheet3.AddMergedRegion(region1f);
sheet3.AddMergedRegion(region2f);
sheet3.AddMergedRegion(region3f);
sheet3.AddMergedRegion(region4f);
sheet3.AddMergedRegion(region5f);
sheet3.AddMergedRegion(regionNf);
sheet3.AddMergedRegion(regionNfP);
#endregion
i = 0; j = 3;
while (i < jl)
{
IRow row = sheet3.CreateRow(j);
int k = 0;
int len = contentDataJL.Rows[i].Table.Columns.Count;
while (k < len)
{
row.CreateCell(k).SetCellValue(contentDataJL.Rows[i][k].ToString());
sheet3.GetRow(j).GetCell(k).CellStyle = ConStyle;
++k;
}
++i;
++j;
}
Picture(workbook, sheet3, 5, jl + 5, 6, jl + 7, addRegFillFileLog.ESID);
}
#endregion
using (FileStream url = File.OpenWrite(filePath))
{
workbook.Write(url);
};
filepath = filePaths;
return filepath;
}数据库中的数据集
最后做一个拓展：
K是列 i是行 j 是输出数据行个数
int i = 0, j = 3;
//K是列 i是行 j 是输出数据行个数
while (i < rc)
{
IRow row = sheet.CreateRow(j);
int k = 0;
int len = contentData.Rows[i].Table.Columns.Count;
while (k < len)
{
if (k > 2)
{
try
{
row.CreateCell(k).SetCellValue(double.Parse(contentData.Rows[i][k].ToString()));
sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
}
catch
{
if (k > 53)
{
row.CreateCell(k).SetCellValue(contentData.Rows[i][k].ToString());
sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
}
}
}
else
{
row.CreateCell(k).SetCellValue(contentData.Rows[i][k].ToString());
sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
}
//sheet.GetRow(j).GetCell(k).CellStyle = ConStyle;
++k;
}
++i;
++j;
}
170
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
