/// <summary>
///创建工作表并将数据加入到工作表中
/// </summary>
/// <param name="ex">操作EXCEL表</param>
/// <param name="bdzlist">填入的数据</param>
/// <param name="hs">一页对应的总行数</param>
/// <param name="stawz">一页中填入的开始的位置</param>
/// <param name="pageindex">一页填入的列位置</param>
/// <param name="strnumcol">第一个字符开始的行数</param>
/// <param name="boldnum">加粗的数量</param>
public static void CreatandWritesheet1(ExcelAccess ex, List<string> bdzlist, int hs, int star, int clm,int[] strnumcol,int[] boldnum)
{
int pageindex = 1;
if (pageindex < Ecommonjh.GetPagecount(bdzlist.Count, hs))
{
pageindex = Ecommonjh.GetPagecount(bdzlist.Count, hs);
}
for (int j = 1; j <= pageindex; j++)
{
if (j > 1)
{
ex.CopySheet(1, 1);
}
}
ex.ShowExcel();
for (int j = 1; j <= pageindex; j++)
{
ex.ActiveSheet(j);
ex.ReNameWorkSheet(j, "Sheet" + (j));
int prepageindex = j - 1;
//主题
int starow = prepageindex * hs + 1;
int endrow = j * hs;
if (bdzlist.Count > endrow)
{
for (int i = 0; i < hs; i++)
{
ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm);
//加粗过程
for (int n = 0; n < strnumcol.Length;n++ )
{
if (starow-1+i==strnumcol[n])
{
ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]);
}
}
//ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4);
//ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5);
//ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6);
//ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7);
//ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8);
//ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9);
}
}
else if (bdzlist.Count <= endrow && bdzlist.Count >= starow)
{
for (int i = 0; i < bdzlist.Count - starow + 1; i++)
{
ex.SetCellValue(bdzlist[starow - 1 + i], star + i, clm);
//加粗过程
for (int n = 0; n < strnumcol.Length; n++)
{
if (starow - 1 + i == strnumcol[n])
{
ex.SetFontBold(star + i, clm, star + i, clm, true, 0, boldnum[n]);
}
}
//ex.SetCellValue(objlist[starow - 1 + i].rq.Month.ToString(), rowcount + i, 1);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Day.ToString(), rowcount + i, 2);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Hour.ToString(), rowcount + i, 3);
//ex.SetCellValue(objlist[starow - 1 + i].rq.Minute.ToString(), rowcount + i, 4);
//ex.SetCellValue(objlist[starow - 1 + i].lxfs, rowcount + i, 5);
//ex.SetCellValue(objlist[starow - 1 + i].yhdz, rowcount + i, 6);
//ex.SetCellValue(objlist[starow - 1 + i].gzjk, rowcount + i, 7);
//ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 8);
//ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 9);
}
}
}
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel1(sdjl_09pxjl obj)
{
//lgm
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\送电18培训记录.xls";
ex.Open(fname);
//每行显示文字长度
int zc = 58;
//与会人员之间的间隔符号
char[] jksign = new char[1] { ';' };
int row = 1;
int col = 1;
//计算页码
int pagecount = 1;
//题目
string tmstr = Ecommon.Comparestring(obj.tm, "题目") ? "" : "题目：";
List<string> tmlist = Ecommon.ResultStrListByPage(tmstr, obj.tm, zc, 4);
//内容
string nrstr = Ecommon.Comparestring(obj.nr, "内容") ? "" : "内容：";
List<string> nrlist = Ecommon.ResultStrListByPage(nrstr, obj.nr, zc, 10);
//领导评语
string ldpystr = Ecommon.Comparestring(obj.py, "领导检查评语") ? "" : "领导检查评语：";
List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 3);
//页数
if (Ecommon.GetPagecount(tmlist.Count + nrlist.Count + ldpylist.Count, 15) > pagecount)
{
pagecount = Ecommon.GetPagecount(ldpylist.Count + tmlist.Count + nrlist.Count, 15);
}
//复制空模版
if (pagecount > 1)
{
for (int i = 1; i < pagecount; i++)
{
ex.CopySheet(1, i);
ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1));
}
}
for (int p = 0; p < pagecount; p++)
{
ex.ActiveSheet(p + 1);
//改造后的
for (int i = 0; i < 17; i++)
{
if (p * 17 + i < tmlist.Count)
{
string tempstr = tmlist[p * 17 + i];
ex.SetCellValue(tempstr, 7 + i, 1);
if (p == 0 && i == 0)
{
//设定活动内容为粗体
ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 3);
}
}
if (p * 17 + i >= tmlist.Count && p * 17 + i < tmlist.Count + nrlist.Count)
{
string tempstr = nrlist[p * 17 + i - tmlist.Count];
ex.SetCellValue(tempstr, 7 + i, 1);
if (p * 17 + i == tmlist.Count)
{
ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 3);
}
//break;
}
if (p * 17 + i >= tmlist.Count + nrlist.Count && p * 17 + i < tmlist.Count + nrlist.Count + ldpylist.Count)
{
string tempstr = ldpylist[p * 17 + i - tmlist.Count - nrlist.Count];
ex.SetCellValue(tempstr, 7 + i, 1);
if (p * 17 + i == tmlist.Count + nrlist.Count)
ex.SetFontBold(7 + i, 1, 7 + i, 1, true, 0, 7);
//break;
}
if (p * 17 + i >= tmlist.Count + nrlist.Count + ldpylist.Count)
{
break;
}
}
}
ex.ActiveSheet(1);
//培训时间
ex.SetCellValue(obj.rq.Year.ToString(), 4, 2);
ex.SetCellValue(obj.rq.Month.ToString(), 4, 4);
ex.SetCellValue(obj.rq.Day.ToString(), 4, 6);
//学习时数
string[] ary = obj.xxss.Split(jksign);
if (ary.Length >= 1)
{
ex.SetCellValue(ary[0], 4, 9);
}
else
{
ex.SetCellValue("", 4, 9);
}
if (ary.Length >= 2)
{
ex.SetCellValue(ary[1], 4, 11);
}
else
{
ex.SetCellValue("", 4, 11);
}
//参加人数
ex.SetCellValue(obj.cjrs, 4, 14);
//主持人
ex.SetCellValue(obj.zcr, 6, 3);
ex.SetCellValue(obj.zjr, 6, 9);
//会议地点
//ex.SetCellValue(obj.hydd, 6, 14);
ex.ShowExcel();
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(PJ_02aqhd obj) {
//lgm
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\02安全活动记录.xls";
ex.Open(fname);
//每行显示文字长度
int zc = 58;
//与会人员之间的间隔符号
char[] jksign = new char[1] { ';' };
int row = 1;
int col = 1;
//计算页码
int pagecount = 1;
//活动内容
string hdstr = Ecommon.Comparestring(obj.hdnr, "活动内容") ? "" : "活动内容：";
List<string> hdlist = Ecommon.ResultStrListByPage(hdstr, obj.hdnr, zc, 8);
//if (Ecommon.GetPagecount(hdlist.Count, 8) > pagecount)
//{
// pagecount = Ecommon.GetPagecount(hdlist.Count, 8);
//}
//活动小结
string hdxjstr = Ecommon.Comparestring(obj.hdxj, "活动小结") ? "" : "活动小结：";
List<string> hdxlist = Ecommon.ResultStrListByPage(hdxjstr, obj.hdxj, zc, 5);
//if (Ecommon.GetPagecount(hdxlist.Count, 5) > pagecount)
//{
// pagecount = Ecommon.GetPagecount(hdxlist.Count, 5);
//}
//发言简要记录
//领导评语
string ldpystr = Ecommon.Comparestring(obj.py, "领导检查评语") ? "" : "领导检查评语：";
List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 2);
if (Ecommon.GetPagecount(ldpylist.Count + hdxlist.Count + hdlist.Count, 15) > pagecount) {
pagecount = Ecommon.GetPagecount(ldpylist.Count + hdxlist.Count + hdlist.Count, 15);
}
List<string> fyjyjllist = Ecommon.ResultStrListByPage("", obj.fyjyjl, zc, 21);
if (Ecommon.GetPagecount(fyjyjllist.Count, 21) > pagecount) {
pagecount = Ecommon.GetPagecount(fyjyjllist.Count, 21);
}
//复制空模版
if (pagecount > 1) {
for (int i = 1; i < pagecount; i++) {
ex.CopySheet(1, i);
ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1));
}
}
for (int p = 0; p < pagecount; p++) {
ex.ActiveSheet(p + 1);
//改造后的
for (int i = 0; i < 15; i++) {
if (p * 15 + i < hdlist.Count) {
string tempstr = hdlist[p * 15 + i];
ex.SetCellValue(tempstr, 10 + i, 1);
if (p == 0 && i == 0) {
//设定活动内容为粗体
ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 5);
}
}
if (p * 15 + i >= hdlist.Count && p * 15 + i < hdlist.Count + hdxlist.Count) {
string tempstr = hdxlist[p * 15 + i - hdlist.Count];
ex.SetCellValue(tempstr, 10 + i, 1);
if (p * 15 + i == hdlist.Count) {
ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 5);
}
//break;
}
if (p * 15 + i >= hdlist.Count + hdxlist.Count && p * 15 + i < hdlist.Count + hdxlist.Count + ldpylist.Count) {
string tempstr = ldpylist[p * 15 + i - hdlist.Count - hdxlist.Count];
ex.SetCellValue(tempstr, 10 + i, 1);
if (p * 15 + i == hdlist.Count + hdxlist.Count)
ex.SetFontBold(10 + i, 1, 10 + i, 1, true, 0, 7);
//break;
}
if (p * 15 + i >= hdlist.Count + hdxlist.Count + ldpylist.Count) {
break;
}
}
////活动内容
//for (int i = 0; i < 8; i++)
//{
// if (p * 8 + i>=hdlist.Count)
// {
// break;
// }
// string tempstr = hdlist[p * 8 + i];
// ex.SetCellValue(tempstr, 10 + i, 1);
//}
////活动小结
//for (int s = 0; s < 5; s++)
//{
// if (p * 5 + s >= hdxlist.Count)
// {
// break;
// }
// string tempstr = hdxlist[p * 5 + s];
// ex.SetCellValue(tempstr, 18 + s, 1);
//}
////领导评语
//for (int t = 0; t < 2; t++)
//{
// if (p * 2 + t >= ldpylist.Count)
// {
// break;
// }
// string tempstr = ldpylist[p * 2 + t];
// ex.SetCellValue(tempstr, 23 + t, 1);
//}
//简要记录
int jymaxpage = Ecommon.GetPagecount(fyjyjllist.Count, 21);
if (pagecount - jymaxpage <= p) {
for (int jy = 0; jy < 21; jy++) {
if ((jymaxpage + p - pagecount) * 21 + jy >= fyjyjllist.Count) {
break;
}
string tempstr = fyjyjllist[(jymaxpage + p - pagecount) * 21 + jy];
ex.SetCellValue(tempstr, 30 + jy, 1);
if (p == pagecount - jymaxpage && jy == 0) {
//设定活动内容为粗体
ex.SetFontBold(30, 1, 30, 1, true, 0, 6);
}
}
}
}
ex.ActiveSheet(1);
//主持人
ex.SetCellValue(obj.zcr, 4, 2);
//开始时间
row = 2;
ex.SetCellValue(obj.kssj.Year.ToString(), row + 2, col + 4);
ex.SetCellValue(obj.kssj.Month.ToString(), row + 2, col + 6);
ex.SetCellValue(obj.kssj.Day.ToString(), row + 2, col + 8);
ex.SetCellValue(obj.kssj.Hour.ToString(), row + 2, col + 10);
ex.SetCellValue(obj.kssj.Minute.ToString(), row + 2, col + 12);
//结束时间
ex.SetCellValue(obj.jssj.Year.ToString(), row + 3, col + 4);
ex.SetCellValue(obj.jssj.Month.ToString(), row + 3, col + 6);
ex.SetCellValue(obj.jssj.Day.ToString(), row + 3, col + 8);
ex.SetCellValue(obj.jssj.Hour.ToString(), row + 3, col + 10);
ex.SetCellValue(obj.jssj.Minute.ToString(), row + 3, col + 12);
//出席人员
string[] ary = obj.cjry.Split(jksign);
int m = ary.Length / 8;
int n = ary.Length % 8;
if (n > 0) {
m++;
}
if (m == 0) {
m++;
}
for (int i = 0; i < ary.Length; i++) {
int tempcol = col + 1 + i % 8;
if (i % 8 > 3) {
tempcol = col + 4 + (i % 8 - 3) * 2;
}
ex.SetCellValue(ary[i], row + 4 + i / 8, tempcol);
}
//缺席人员
string[] ary2 = obj.qxry.Split(jksign);
for (int j = 0; j < ary2.Length; j++) {
int tempcol = col + 2 + j % 7;
if (j > 2 && j < 7) {
tempcol = col + 4 + (j % 7 - 2) * 2;
}
if (j < 7) {
ex.SetCellValue(ary2[j], row + 7, tempcol);
} else//缺席人员大于七个时
{
string tempstr = ex.ReadCellValue(row + 7, col + 12);
tempstr = tempstr + "/" + ary2[j];
ex.SetCellValue(tempstr, row + 7, col + 12);
}
}
//签字
// ex.SetCellValue("签字: ", 25, 1);
// ex.SetCellValue(obj.qz, 25, col + 1);
//签字时间小于系统默认时间则不输出
// if (ComboBoxHelper.CompreDate(obj.qzrq))
// {
// ex.SetCellValue(obj.qzrq.Year.ToString(), 25, 5);
// ex.SetCellValue(obj.qzrq.Month.ToString(), 25, 7);
// ex.SetCellValue(obj.qzrq.Day.ToString(), 25, 11);
// }
ex.ShowExcel();
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(sdjls_fsgyxjl obj)
{
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\送电05反事故演习记录.xls";
ex.Open(fname);
ex.ActiveSheet(1);
//每行显示文字长度
int zc = 39;
//日期
ex.SetCellValue(obj.yxrq.Year.ToString(),4,2);
ex.SetCellValue(obj.yxrq.Month.ToString(), 4, 4);
ex.SetCellValue(obj.yxrq.Day.ToString(), 4, 6);
//单位
ex.SetCellValue(obj.yxdd,4,11);
//时间
ex.SetCellValue(obj.kssj.Hour.ToString(),5,2);
ex.SetCellValue(obj.kssj.Minute.ToString(), 5, 6);
ex.SetCellValue(obj.jssj.Hour.ToString(), 5, 11);
ex.SetCellValue(obj.jssj.Minute.ToString(),5,13);
//演习地点
ex.SetCellValue(obj.yxdd, 6, 3);
//演习题目
ex.SetCellValue(obj.yxtm, 7, 3);
ex.SetCellValue(obj.ldr, 8, 3);
ex.SetCellValue(obj.jhr, 8, 11);
string jljpj = Ecommon.Comparestring(obj.jljpj, "结论及对参加演习人的评价") ? "" : "结论及对参加演习人的评价:";
jljpj = jljpj + obj.jljpj;
//结论及对参加演习人的评价
for (int i = 0; i < 8; i++)
{
string tempstr = "";
int startnum = i * zc;
int endnum = (i + 1) * zc;
bool ISempty = false;
if (startnum >= jljpj.Length)
{
ISempty = true;
}
else if (endnum >= jljpj.Length)
{
endnum = jljpj.Length;
}
if (!ISempty)
{
tempstr = jljpj.Substring(startnum, endnum - startnum);
}
ex.SetCellValue(tempstr, 9 + i, 1);
if (i == 0)
{
ex.SetFontBold(9, 1, 9, 1, true, 1, 13);
}
}
//根据演习结果拟定的措施
int cszs = 23;
for (int i = 0; i < 4; i++)
{
string tempstr = "";
int startnum = i * cszs;
int endnum = (i + 1) * cszs;
bool ISempty = false;
if (startnum >= obj.ndcs.Length)
{
ISempty = true;
}
else if (endnum >= obj.ndcs.Length)
{
endnum = obj.ndcs.Length;
}
if (!ISempty)
{
if (i == 0)
{
if (endnum == obj.ndcs.Length)
{
tempstr = obj.ndcs.Substring(startnum, endnum - startnum);
}
else
{
tempstr = obj.ndcs.Substring(startnum, endnum - startnum - 2);
}
}
else
{
if (endnum == obj.ndcs.Length)
{
tempstr = obj.ndcs.Substring(startnum - 2, endnum - startnum+1);
}
else
{
tempstr = obj.ndcs.Substring(startnum - 2, endnum - startnum);
}
}
}
ex.SetCellValue(tempstr, 18 + i, 1);
}
//执行人
ex.SetCellValue(obj.zxr, 18, 10);
ex.SetCellValue(obj.zgxq.Year+"年"+obj.zgxq.Month+"月"+obj.zgxq.Day+"日", 18, 11);
ex.SetCellValue(obj.qzldr, 22, 2);
ex.SetCellValue(obj.qzjhr,22,10);
ex.ShowExcel();
}
