/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(sdjl_26 obj) {
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\送电17防护通知书.xls";
int sz = 30;//一行最多30个
ex.Open(fname);
//此处写填充内容代码
ex.ActiveSheet(1);
//通知单位
ex.SetCellValue(obj.tzdw, 5, 1);
//电压
ex.SetCellValue(obj.lineVol, 6, 7);
//线路
ex.SetCellValue(obj.c1, 6, 9);
//发现问题
List<string> fxwtList = GetList(obj.fxwt, sz);
for (int i = 0; i < fxwtList.Count; i++)
{
ex.SetCellValue(fxwtList[i], 7+i, 1);
if (i > 1)
break;
}
//处理措施
List<string> clcsList = GetList(obj.clcs, sz);
for (int i = 0; i < clcsList.Count; i++)
{
ex.SetCellValue(obj.clcs, 12+i, 1);
if (i > 2)
break;
}
ex.ShowExcel();
}
void gridViewOperation_AfterAdd(PJ_24 e)
{
DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
Microsoft.Office.Interop.Excel.Workbook wb;dsoFramerControl1.FileData = e.BigData;
wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
ea.SetCellValue(ParentObj.OrgName , 4, 9);
dsoFramerControl1.FileSave();
e.BigData = dsoFramerControl1.FileData;
dsoFramerControl1.FileClose();
dsoFramerControl1.Dispose();
dsoFramerControl1 = null;
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(PJ_24 jl)
{
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls";
ex.Open(fname);
int row = 1;
int col = 1;
//
ex.SetCellValue(jl.sj.Year.ToString(), row + 8, col);
ex.SetCellValue(jl.sj.Month.ToString(), row + 8, col+2);
ex.SetCellValue(jl.sj.Day.ToString(), row + 8, col+4);
ex.SetCellValue(jl.dd, row + 5, col+6);
ex.SetCellValue(jl.nr, row + 5, col + 7);
ex.SetCellValue(jl.Remark, row + 5, col + 10);
ex.ShowExcel();
}
private void btnOK_Click(object sender, EventArgs e)
{
if (rowData.BigData.Length == 0)
{
mOrg org = MainHelper.PlatformSqlMap.GetOneByKey<mOrg>(rowData.ParentID);
string fname = Application.StartupPath + "\\00记录模板\\23配电线路产权维护范围协议书.xls";
string bhname = org.OrgName.Replace("供电所", "");
DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
dsoFramerControl1.FileOpen(fname);
Microsoft.Office.Interop.Excel.Workbook wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
//PJ_23 obj = (PJ_23)MainHelper.PlatformSqlMap.GetObject("SelectPJ_23List", "where ParentID='" + rowData.ParentID + "' and xybh like '" + SelectorHelper.GetPysm(org.OrgName.Replace("供电所", ""), true) + "" + DateTime.Now.Year.ToString() + "%' order by xybh ASC");
//int icount = 1;
//if (obj != null && obj.xybh !="")
//{
// icount = Convert.ToInt32(obj.xybh.Split('-')[2])+1;
//}
//string strname = SelectorHelper.GetPysm(bhname, true);
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
ea.SetCellValue(rowData.xybh.ToUpper(), 4, 8);
//strname = DateTime.Now.Year.ToString();
//ea.SetCellValue(strname, 4, 9);
//strname = string.Format("{0:D3}", icount);
//ea.SetCellValue(strname, 4, 10);
ea.SetCellValue(rowData.linename, 10, 7);
ea.SetCellValue(rowData.fzlinename, 10, 10);
ea.SetCellValue("'" + rowData.gh, 10, 16);
ea.SetCellValue(rowData.cqfw, 11, 4);
ea.SetCellValue(rowData.cqdw, 13, 4);
ea.SetCellValue(rowData.qdrq.Year.ToString(), 21, 7);
ea.SetCellValue(rowData.qdrq.Month.ToString(), 21, 9);
ea.SetCellValue(rowData.qdrq.Day.ToString(), 21, 11);
dsoFramerControl1.FileSave();
rowData.BigData = dsoFramerControl1.FileData;
dsoFramerControl1.FileClose();
dsoFramerControl1.Dispose();
dsoFramerControl1 = null;
//rowData.xybh = SelectorHelper.GetPysm(bhname, true).ToUpper() + "-" + DateTime.Now.Year.ToString() + "-" + string.Format("{0:D3}", icount);
}
DSOFramerControl dsoFramerControl2 = new DSOFramerControl();
dsoFramerControl2.FileData = rowData.BigData;
Microsoft.Office.Interop.Excel.Workbook wb2 = dsoFramerControl2.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
ExcelAccess ea2 = new ExcelAccess();
ea2.MyWorkBook = wb2;
ea2.MyExcel = wb2.Application;
ea2.SetCellValue(comboBoxEdit1.Text, 11, 4);
dsoFramerControl2.FileSave();
rowData.BigData = dsoFramerControl2.FileData;
dsoFramerControl2.FileClose();
dsoFramerControl2.Dispose();
dsoFramerControl2 = null;
}
/// <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);
}
}
}
}
private void btnOK_Click(object sender, EventArgs e)
{
//if (comboBoxEdit1.Text == "")
//{
// MsgBox.ShowTipMessageBox("变动地点不能为空。");
// comboBoxEdit1.Focus();
// return;
//}
////if (rowData.BigData == null)
////{
//// rowData.BigData = new byte[0];
////}
//this.DialogResult = DialogResult.OK;
//this.Close();
IList<PJ_24> list = Client.ClientHelper.PlatformSqlMap.GetList<PJ_24>("where ParentID='" + rowData.ParentID + "'");
string bh = (list.Count + 1).ToString("000");
DSOFramerControl dsoFramerControl1 = new DSOFramerControl();
Microsoft.Office.Interop.Excel.Workbook wb;
if (rowData.BigData == null || rowData.BigData.Length == 0)
{
string fname = Application.StartupPath + "\\00记录模板\\24设备变更通知书.xls";
dsoFramerControl1.FileOpen(fname);
}
else
dsoFramerControl1.FileData = rowData.BigData;
wb = dsoFramerControl1.AxFramerControl.ActiveDocument as Microsoft.Office.Interop.Excel.Workbook;
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
DateTime dt = Convert.ToDateTime(dateEdit1.EditValue);
ea.SetCellValue(dt.Year.ToString(), 4, 7);
ea.SetCellValue(bh, 4, 10);
ea.SetCellValue(dt.Year.ToString(), 9, 1);
ea.SetCellValue(dt.Month.ToString(), 9, 3);
ea.SetCellValue(dt.Day.ToString(), 9, 5);
ea.SetCellValue(comboBoxEdit1.Text, 6, 7);
ea.SetCellValue(memoEdit1.Text, 6, 8);
ea.SetCellValue(memoEdit2.Text, 6, 11);
dsoFramerControl1.FileSave();
rowData.BigData = dsoFramerControl1.FileData;
dsoFramerControl1.FileClose();
dsoFramerControl1.Dispose();
dsoFramerControl1 = null;
}
public void ExportExcel(ExcelAccess ex, IList<PJ_sdytz> datalist)
{
//此处写填充内容代码
int row = 5;
int col = 1;
int rowcount =14;
//
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
{
pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
}
for (int j = 1; j <= pageindex; j++)
{
if (j > 1)
{
ex.CopySheet(1, 1);
}
}
for (int j = 0; j < datalist.Count; j++)
{
if (j % rowcount == 0)
{
ex.ActiveSheet(j / rowcount + 1);
}
ex.SetCellValue(((j + 1)).ToString(), row + j % rowcount, col);
ex.SetCellValue(datalist[j].khmc, row + j % rowcount, col + 1);
ex.SetCellValue(datalist[j].khdz, row + j % rowcount, col + 2);
ex.SetCellValue(datalist[j].zdyOrgName, row + j % rowcount, col + 3);
ex.SetCellValue(datalist[j].zdyLineName, row + j % rowcount, col + 4);
ex.SetCellValue(datalist[j].zdykgModle, row + j % rowcount, col + 5);
ex.SetCellValue(datalist[j].fdyOrgName, row + j % rowcount, col + 6);
ex.SetCellValue(datalist[j].fdyLineName, row + j % rowcount, col + 7);
ex.SetCellValue(datalist[j].fdykgModle, row + j % rowcount, col + 8);
ex.SetCellValue(datalist[j].kgfs, row + j % rowcount, col + 9);
ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 10);
}
}
/// <summary>
/// 设置密封数据
/// </summary>
/// <param name="ex"></param>
/// <param name="list"></param>
/// <param name="modmflist"></param>
/// <param name="jstrfilter"></param>
/// <param name="strfilter"></param>
/// <param name="jstart"></param>
/// <param name="j"></param>
/// <param name="jmax"></param>
/// <param name="istart2"></param>
/// <param name="imf"></param>
public static void setExcelmfdt(ExcelAccess ex, IList list, IList modmflist, string jstrfilter, string strfilter, int jstart, int j, int jmax, int istart2,int imf)
{
//string str = "select b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50)) and (b.omniseal='true' )" + jstrfilter;
string str = "select b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.byqModle like '" + modmflist[imf] + "%' and (b.omniseal='true' )" + jstrfilter;
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str);
if (list.Count > 0) ex.SetCellValue(list.Count.ToString(), istart2 + (imf % 3) * 2, jstart + j % jmax);
//str = "select sum( b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.byqModle='" + modmflist[imf] + "-'+cast(b.byqCapcity as nvarchar(50))+'/'+ cast(b.byqVol as nvarchar(50)) and (b.omniseal='true' )" + jstrfilter;
str = "select sum( b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.byqModle like '" + modmflist[imf] + "%' and (b.omniseal='true' )" + jstrfilter;
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", str);
//if (list[0] == null) list[0] = 0;
if (list[0] != null)
ex.SetCellValue(list[0].ToString(), istart2 + (imf % 3) * 2 + 1, jstart + j % jmax);
}
/// <summary>
/// 文档格式预定义好的，动态填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExceldt(string obj)
{
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\10配电变压器汇总表.xls";
ex.Open(fname );
IList list=new ArrayList ();
int pagecount=0;
//obj = "317";
string strfilter = " and 1=1";
if (obj != "") strfilter = strfilter + " and a.OrgCode='" + obj+"' ";
IList caplist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select distinct b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " order by b.byqCapcity");
//caplist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", string.Format("select nr from pj_dyk where len(parentid)>1 and dx='{0}' and sx='{1}'", "11配电变压器卡片", "容量"));
//IList modmflist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(replace(b.byqModle,'-'+cast(b.byqCapcity as nvarchar(50))+'/'+cast(b.byqVol as nvarchar(50)),'')) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.omniseal='true'");
//IList modtmlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(replace(b.byqModle,'-'+cast(b.byqCapcity as nvarchar(50))+'/'+cast(b.byqVol as nvarchar(50)),'')) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and (b.omniseal!='true' or b.omniseal is null)");
IList modmflist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(b.byqModle) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and b.omniseal='true'");
IList modtmlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select distinct UPPER(b.byqModle) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + " and (b.omniseal!='true' or b.omniseal is null)");
int jmax = 18;
int i = 0;
for ( i = 0; i < modmflist.Count; i++)
{
if (modmflist[i].ToString().IndexOf("-")>-1)
{
modmflist[i] = modmflist[i].ToString().Substring(0,modmflist[i].ToString().IndexOf("-"));
}
}
for (i = 0; i < modtmlist.Count; i++)
{
if (modtmlist[i].ToString().IndexOf("-")>-1)
{
modtmlist[i] = modtmlist[i].ToString().Substring(0, modtmlist[i].ToString().IndexOf("-"));
}
}
string modtemp = "";
for (i = 0; i < modmflist.Count; i++)
{
if (modtemp.IndexOf(modmflist[i].ToString())==-1)
{
if (modtemp!="")
modtemp+=","+modmflist[i];
else
modtemp = modmflist[i].ToString();
}
}
if (modtemp != "")
{
string[] strli = modtemp.Split(',');
modmflist.Clear();
for (i = 0; i < strli.Length; i++)
{
modmflist.Add(strli[i]);
}
}
for (i = 0; i < modtmlist.Count; i++)
{
if (modtemp.IndexOf(modtmlist[i].ToString()) == -1)
{
if (modtemp!="")
modtemp += "," + modtmlist[i];
else
modtemp = modtmlist[i].ToString();
}
}
if (modtemp != "")
{
string[] strli = modtemp.Split(',');
modtmlist.Clear();
for (i = 0; i < strli.Length; i++)
{
modtmlist.Add(strli[i]);
}
}
pagecount = (int)Math.Ceiling(caplist.Count / (jmax + 0.0));
int itemp = modtmlist.Count / 6.0 > modmflist.Count / 3.0 ? (int)Math.Ceiling(modtmlist.Count / 6.0) : (int)Math.Ceiling(modmflist.Count / 3.0 );
pagecount = itemp * pagecount;
if (pagecount > 1)
{
ex.DeleteWorkSheet("Sheet2");
ex.DeleteWorkSheet("Sheet3");
}
/////计算需要多少个工作表
for (i = 1; i < pagecount; i++)
{
ex.CopySheet(1,i);
ex.ReNameWorkSheet((i + 1), "Sheet" + (i + 1));
}
int istart = 7, istart2 = 19, jstart = 4, j=0;
string jstrfilter="";
mOrg org = Client.ClientHelper.PlatformSqlMap.GetOneByKey<mOrg>(obj);
for ( j = 0; j < caplist.Count; j++)
{
ex.ActiveSheet("Sheet" + (j / jmax == 0 ? 1 : (int)Math.Ceiling(j / (jmax + 0.0))));
//ex.ActiveSheet("Sheet" + (1 + (j / jmax) * (int)Math.Ceiling(j / (jmax + 0.0))));
if (j % jmax == 0)
{
jstrfilter = " and 1=1";
ex.ActiveSheet("Sheet" + (1+(j / jmax)*(int)Math.Ceiling(j / (jmax + 0.0))));
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter);
if (list.Count >0) ex.SetCellValue(list.Count.ToString(), 5, jstart + j % jmax);
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select sum(b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter);
//if (list[0] == null) list[0] = 0;
if (list[0] != null)
ex.SetCellValue(list[0].ToString(), 6, jstart + j % jmax);
setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart, istart2);
}
ex.ActiveSheet("Sheet" + ((j+1) / jmax == 0 ? 1 : (int)Math.Ceiling((j+1) / (jmax + 0.0))));
jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'";
//ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);
setExceldt(ex, list, caplist, modtmlist, modmflist, jstrfilter, strfilter, jstart+1, j, jmax, istart, istart2);
}
for (i = 0; i < pagecount; i++)
{
ex.ActiveSheet("Sheet" + (i+1));
int jzu=caplist.Count/jmax+1;
int itempmin = 0, itempmax = 0;
itempmax = (i % jzu+1) * jmax;
itempmin = (i % jzu) * jmax;
if (org != null)
ex.SetCellValue(org.OrgName, 3, 3);
else
ex.SetCellValue("全局", 3, 3);
for (j = itempmin; j < itempmax && j < caplist.Count ; j++)
{
ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);
}
//ex.SetCellValue(caplist[j].ToString(), 4, jstart + j % jmax + 1);
}
ex.ActiveSheet(1);
ex.ShowExcel();
}
public static int ExportToExcel(string title, string dw, sdjls_sbpjb pj17) {
string fname = Application.StartupPath + "\\00记录模板\\送管22送电线路设备评级表.xls";
DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl();
string outfname = Path.GetTempFileName() + ".xls";
File.Copy(fname, outfname);
dsoFramerWordControl1.FileOpen(outfname);
Microsoft.Office.Interop.Excel.Worksheet xx;
Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook;
ExcelAccess ex = new ExcelAccess();
ex.MyWorkBook = wb;
ex.MyExcel = wb.Application;
sd_xl xl = MainHelper.PlatformSqlMap.GetOne<sd_xl>(" where LineCode='" + pj17.LineCode + "'");
try {
if (xl == null) {
MsgBox.ShowWarningMessageBox("数据出错，没找到线路");
return -1;
}
string strLinexh = xl.WireType;//导线型号
//IList<sd_gt> gtlis = Client.ClientHelper.PlatformSqlMap.GetList<sd_gt>(" Where LineCode='" + xl.LineCode + "' order by gtcode");
int gtcount = Client.ClientHelper.PlatformSqlMap.GetRowCount<sd_gt>(" Where LineCode='" + xl.LineCode + "'");
ex.ActiveSheet(1);
//设置线路值
ex.SetCellValue(xl.LineName, 3, 3);
ex.SetCellValue(xl.LineVol, 3, 8);
ex.SetCellValue(xl.WireLength.ToString(), 3, 11);
ex.SetCellValue(xl.InDate.Year + "年" + xl.InDate.Month + "月", 3, 13);
//ex.SetCellValue(xl.InDate.Year.ToString(), 4, 9);
//ex.SetCellValue(xl.InDate.Month.ToString(), 4, 11);
//评级日期
ex.SetCellValue(DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day + "日", 4, 3);
//杆塔
ex.SetCellValue(gtcount.ToString(), 5, 3);//合计
ex.SetCellValue(gtcount.ToString(), 6, 3);//一类
//导地线
ex.SetCellValue(xl.WireLength.ToString(), 9, 3);//合计
ex.SetCellValue(xl.WireLength.ToString(), 10, 3);//一类
//绝缘子
string sql = "in (";
//foreach (sd_gt gt in gtlis)
//{
// sql+="'"+gt.gtID+"',";
//}
//sql = sql.Substring(0, sql.Length - 1) + ")";
sql = "in (select gtid from sd_gt Where LineCode='" + xl.LineCode + "' )";
string strSQL = "select sbid from sd_gtsb Where gtID " + sql;
IList jdzzList = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL);
strSQL += " and ("+getNamesql()+")";
IList jyuzlist = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", strSQL);
ex.SetCellValue(jyuzlist.Count.ToString(), 13, 3);
ex.SetCellValue(jyuzlist.Count.ToString(), 14, 3);
//接地装置
ex.SetCellValue(jdzzList.Count.ToString(), 17, 3);
ex.SetCellValue(jdzzList.Count.ToString(), 18, 3);
//其它
//评定等级
ex.SetCellValue("一级", 24, 3);
//评级负责人
if (MainHelper.User != null)
{
ex.SetCellValue(MainHelper.User.UserName, 25, 3);
}
} catch (Exception exmess) {
MsgBox.ShowTipMessageBox(exmess.Message.ToString());
}
dsoFramerWordControl1.FileSave();
pj17.BigData = dsoFramerWordControl1.FileDataGzip;
dsoFramerWordControl1.FileClose();
dsoFramerWordControl1.Dispose();
//#endregion
//System.Diagnostics.Process.Start(outfname);
return 1;
}
public void ExportExcel(ExcelAccess ex, IList<PJ_sbbzqsbgmxb4> datalist, string orgid)
{
//此处写填充内容代码
int row = 7;
int col = 1;
int rowcount = 13;
//
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
{
pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
}
for (int j = 1; j < pageindex; j++)
{
ex.CopySheet(1, j);
}
for (int j = 0; j < datalist.Count; j++)
{
if (j % rowcount == 0)
{
if (j == 0) ex.ActiveSheet(1);
else ex.ActiveSheet((j / rowcount + 1));
if (orgid != "") ex.SetCellValue(datalist[j].OrgName, 4, 2);
else
ex.SetCellValue(MainHelper.UserCompany, 4, 2);
ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"), 4, 12);
}
ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col);
ex.SetCellValue(datalist[j].sssbmc, row + j % rowcount, col + 1);
ex.SetCellValue(datalist[j].sssswz, row + j % rowcount, col + 2);
string[] tempstr = datalist[j].sssbbh.Split(',');
try
{
ex.SetCellValue(tempstr[0], row + j % rowcount, col + 3);
ex.SetCellValue(tempstr[1], row + j % rowcount, col + 4);
ex.SetCellValue(tempstr[2], row + j % rowcount, col + 5);
}
catch
{
}
string[] tempstr1 = datalist[j].S1.Split(',');
try
{
ex.SetCellValue(tempstr1[0], row + j % rowcount, col + 6);
ex.SetCellValue(tempstr1[1], row + j % rowcount, col + 7);
ex.SetCellValue(tempstr1[2], row + j % rowcount, col + 8);
ex.SetCellValue(tempstr1[3], row + j % rowcount, col + 9);
}
catch
{
}
ex.SetCellValue(datalist[j].xw, row + j % rowcount, col + 10);
ex.SetCellValue(datalist[j].statuts, row + j % rowcount, col + 11);
}
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(IList<PJ_qxfl> objlist)
{
#region 原先的
//if (objlist == null || objlist.Count == 0)
//{
// return;
//}
////lgm
//ExcelAccess ex = new ExcelAccess();
//SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls";
//ex.Open(fname);
////每行显示文字长度
//int zc = 20;
////与会人员之间的间隔符号
//char[] jksign = new char[1] { ';' };
//int row = 6;
//int col = 2;
//int len1 =1;
//ex.SetCellValue(objlist[0].OrgName, 3, 2);
//int pagecout = Ecommon.GetPagecount(objlist.Count,12);
////复制空模板
//for (int m = 1; m < pagecout; m++)
//{
// ex.CopySheet(1, m);
// ex.ReNameWorkSheet(m + 1, "Sheet" + (m + 1));
//}
//for (int p = 0; p < pagecout; p++)
//{
// ex.ActiveSheet(p + 1);
// for (int i = 0; i < 9; i++)
// {
// if (p*9+i>=objlist.Count)
// {
// break;
// }
// PJ_qxfl tempobj = objlist[p *12 + i];
// //缺陷情况
// ex.SetCellValue(tempobj.qxnr, row + i * len1, 2);
// //缺陷类别
// ex.SetCellValue(tempobj.qxlb, row + i * len1,3);
// //发现日期
// if (ComboBoxHelper.CompreDate(tempobj.xssj))
// {
// ex.SetCellValue(tempobj.xssj.Year.ToString(), row + i * len1, 4);
// ex.SetCellValue(tempobj.xssj.Month.ToString(), row + i * len1, 5);
// ex.SetCellValue(tempobj.xssj.Day.ToString(), row + i * len1, 6);
// }
// //消除时限
// if (tempobj.xcqx!=string.Empty)
// {
// if (ComboBoxHelper.CompreDate(Convert.ToDateTime(tempobj.xcqx)))
// {
// ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Year.ToString(), row + i * len1, 7);
// ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Month.ToString(), row + i * len1, 8);
// ex.SetCellValue(Convert.ToDateTime(tempobj.xcqx).Day.ToString(), row + i * len1, 9);
// }
// }
// //备注
// ex.SetCellValue(tempobj.remark, row + i * len1,10);
// }
//}
//ex.ActiveSheet(1);
//ex.ShowExcel();
#endregion
if (objlist == null || objlist.Count == 0)
{
return;
}
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\缺陷分类统计表.xls";
ex.Open(fname);
ex.SetCellValue(objlist[0].OrgName, 2, 2);
for (int i = 0; i < objlist.Count; i++)
{
ex.SetCellValue((i + 1).ToString(), i +4, 1);
ex.SetCellValue(objlist[i].qxly, i + 4, 2);
ex.SetCellValue(objlist[i].xlqd, i + 4, 3);
ex.SetCellValue(objlist[i].xssj.ToString("yyyy年MM月dd日"), i + 4, 4);
ex.SetCellValue(objlist[i].qxnr, i + 4, 5);
ex.SetCellValue(objlist[i].qxlb, i + 4, 6);
ex.SetCellValue(objlist[i].xcqx, i + 4, 7);
}
ex.ActiveSheet(1);
ex.ShowExcel();
}
public void ExportExcel(ExcelAccess ex ,IList<PJ_clcrkd> datalist)
{
//此处写填充内容代码
int row = 7;
int col = 1;
int rowcount = 10;
//
if (datalist.Count < 1) return;
Regex r1 = new Regex("[0-9]+");
string str = r1.Match(datalist[0].num).Value;
if (str == "")
{
str = datalist[0].num;
}
string tablename = datalist[0].ssgc + datalist[0].ssxm + str;
if (tablename.Length > 30)
{
tablename = tablename.Substring(tablename.Length - 31);
}
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
{
pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
}
for (int j = 1; j <= pageindex; j++)
{
ex.CopySheet(1, j);
if (j == 1) ex.ReNameWorkSheet(j + 1, tablename);
else
ex.ReNameWorkSheet(j + 1, tablename + "(" + (j) + ")");
}
for (int j = 0; j < datalist.Count; j++)
{
if (j % rowcount == 0)
{
if (j == 0) ex.ActiveSheet(tablename);
else ex.ActiveSheet(tablename + "(" + (j / rowcount + 1) + ")");
ex.SetCellValue(datalist[j].ssgc, 2, 3);
ex.SetCellValue(datalist[j].ssxm, 4, 2);
ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 4, 7);
ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 4, 9);
ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 4, 11);
ex.SetCellValue(datalist[j].ssgc, 22, 3);
ex.SetCellValue(datalist[j].ssxm, 24, 2);
ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 24, 7);
ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 24, 9);
ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 24, 11);
ex.SetCellValue(datalist[j].ssgc, 42, 3);
ex.SetCellValue(datalist[j].ssxm, 44, 2);
ex.SetCellValue(datalist[j].ckdate.ToString("yyyy"), 44, 7);
ex.SetCellValue(datalist[j].ckdate.ToString("MM"), 44, 9);
ex.SetCellValue(datalist[j].ckdate.ToString("dd"), 44, 11);
ex.SetCellValue(datalist[j].ssgc, 62, 3);
ex.SetCellValue(datalist[j].ssxm, 64, 2);
ex.SetCellValue(datalist[j].indate.ToString("yyyy"), 64, 7);
ex.SetCellValue(datalist[j].indate.ToString("MM"), 64, 9);
ex.SetCellValue(datalist[j].indate.ToString("dd"), 64, 11);
}
ex.SetCellValue(datalist[j].wpmc, row + j % rowcount , col);
ex.SetCellValue(datalist[j].wpgg, row + j % rowcount , col + 2);
ex.SetCellValue(datalist[j].wpdw, row + j % rowcount, col + 4);
ex.SetCellValue(datalist[j].cksl, row + j % rowcount, col + 5);
ex.SetCellValue(datalist[j].wpdj, row + j % rowcount , col + 7);
ex.SetCellValue(datalist[j].wpmc, row + j % rowcount+20, col);
ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 20, col + 2);
ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 20, col + 4);
ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 20, col + 5);
ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 20, col + 7);
ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 40, col);
ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 40, col + 2);
ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 40, col + 4);
ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 40, col + 5);
ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 40, col + 7);
ex.SetCellValue(datalist[j].wpmc, row + j % rowcount + 60, col);
ex.SetCellValue(datalist[j].wpgg, row + j % rowcount + 60, col + 2);
ex.SetCellValue(datalist[j].wpdw, row + j % rowcount + 60, col + 4);
ex.SetCellValue(datalist[j].cksl, row + j % rowcount + 60, col + 5);
ex.SetCellValue(datalist[j].wpdj, row + j % rowcount + 60, col + 7);
if (datalist[j].wpdj != "")
{
long value = Convert.ToInt64(Math.Round(Convert.ToDouble(datalist[j].cksl) * Convert.ToDouble(datalist[j].wpdj), 2) * 100);
int index = 19;
while (value > 0)
{
long ifen = value % 10;
ex.SetCellValue(ifen.ToString(), row + j % rowcount, index);
ex.SetCellValue(ifen.ToString(), row + j % rowcount + 20, index);
ex.SetCellValue(ifen.ToString(), row + j % rowcount + 40, index);
ex.SetCellValue(ifen.ToString(), row + j % rowcount + 60, index);
value = value / 10;
index--;
}
}
ex.SetCellValue(datalist[j].Remark, 17, 2);
ex.SetCellValue(datalist[j].Remark, 17 + 20, 2);
ex.SetCellValue(datalist[j].Remark, 17 + 40, 2);
ex.SetCellValue(datalist[j].Remark, 17 + 60, 2);
//ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7);
}
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(PJ_09pxjl obj) {
//lgm
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\09培训记录.xls";
ex.Open(fname);
//每行显示文字长度
int zc = 60;
//与会人员之间的间隔符号
char[] jksign = new char[1] { ';' };
int row = 1;
int col = 1;
//计算页码
int pagecount = 1;
//题目
string timustr = "题目：";
List<string> timulist = Ecommon.ResultStrListByPage(timustr, obj.tm, zc, 4);
if (Ecommon.GetPagecount(timulist.Count, 4) > pagecount) {
pagecount = Ecommon.GetPagecount(timulist.Count, 4);
}
//活动内容
string hdstr = "内容：";
List<string> hdlist = Ecommon.ResultStrListByPage(hdstr, obj.nr, zc, 10);
if (Ecommon.GetPagecount(hdlist.Count, 10) > pagecount) {
pagecount = Ecommon.GetPagecount(hdlist.Count, 10);
}
//领导评语
string ldpystr = "领导检查评语：";
List<string> ldpylist = Ecommon.ResultStrListByPage(ldpystr, obj.py, zc, 3);
if (Ecommon.GetPagecount(ldpylist.Count, 3) > pagecount) {
pagecount = Ecommon.GetPagecount(ldpylist.Count, 3);
}
//复制空模板
for (int m = 1; m < pagecount; m++) {
ex.CopySheet(1, m);
ex.ReNameWorkSheet(m + 1, "Sheet" + (m + 1));
}
for (int p = 0; p < pagecount; p++) {
ex.ActiveSheet(p + 1);
//题目
for (int i = 0; i < 4; i++) {
if (p * 4 + i >= timulist.Count) {
break;
}
string tempstr = timulist[p * 4 + i];
ex.SetCellValue(tempstr, 7 + i, 1);
}
//活动内容
for (int r = 0; r < 10; r++) {
if (p * 10 + r >= hdlist.Count) {
break;
}
string tempstr = hdlist[p * 10 + r];
ex.SetCellValue(tempstr, 11 + r, 1);
}
//领导评语
for (int t = 0; t < 3; t++) {
if (p * 3 + t >= ldpylist.Count) {
break;
}
string tempstr = ldpylist[p * 3 + t];
ex.SetCellValue(tempstr, 21 + t, 1);
}
}
ex.ActiveSheet(1);
//培训时间
row = 2;
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.SetCellValue(obj.qz, 24, 3);
// if (ComboBoxHelper.CompreDate(obj.qzrq))
// {
// ex.SetCellValue(obj.qzrq.Year.ToString(), 24, 8);
// ex.SetCellValue(obj.qzrq.Month.ToString(), 24, 10);
// ex.SetCellValue(obj.qzrq.Day.ToString(), 24, 12);
// }
ex.ActiveSheet(1);
ex.ShowExcel();
}
private static void ExportExcel(PJ_23 obj, ExcelAccess ex)
{
ex.SetCellValue(obj.jf + ":", 5, 4);
ex.SetCellValue(obj.xybh, 4, 8);
ex.SetCellValue(obj.cqdw + "：", 6, 4);
string linename = "";
if (obj.linename.Contains("线"))
{
linename = obj.linename.Substring(0, obj.linename.LastIndexOf("线"));
}
else
linename = obj.linename;
string[] filtchar = { "V", "v" };
for (int i = 0; i < filtchar.Length; i++)
{
if (linename.Contains(filtchar[i]))
{
linename = linename.Substring(linename.LastIndexOf(filtchar[i]) + 1);
}
}
ex.SetCellValue(linename, 10, 7);
string fzlinename = "";
if (obj.fzlinename.Contains("支"))
{
fzlinename = obj.fzlinename.Substring(0, obj.fzlinename.LastIndexOf("支"));
}
else
fzlinename = obj.fzlinename;
ex.SetCellValue(fzlinename, 10, 10);
ex.SetCellValue("'" + obj.gh, 10, 16);
ex.SetCellValue(obj.cqfw, 11, 4);
ex.SetCellValue(obj.cqdw + "。", 13, 4);
ex.SetCellValue(obj.jf, 17, 4);
//ex.SetCellValue(obj.cqdw, 15, 8);
ex.SetCellValue(obj.qdrq.Year.ToString(), 21, 7);
ex.SetCellValue(obj.qdrq.Month.ToString(), 21, 9);
ex.SetCellValue(obj.qdrq.Day.ToString(), 21, 11);
}
public void ExportExcel(ExcelAccess ex, IList<PJ_sbbzqsbgmxb3> datalist, string orgid)
{
//此处写填充内容代码
int row = 6;
int col = 1;
int rowcount = 15;
//
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(datalist.Count, rowcount))
{
pageindex = Ecommon.GetPagecount(datalist.Count, rowcount);
}
for (int j = 1; j <pageindex; j++)
{
ex.CopySheet(1, j);
}
for (int j = 0; j < datalist.Count; j++)
{
if (j % rowcount == 0)
{
if(j==0)ex.ActiveSheet(1);
else ex.ActiveSheet((j / rowcount+1) );
if (orgid!="") ex.SetCellValue(datalist[j].OrgName, 4, 2);
else
ex.SetCellValue(MainHelper.UserCompany, 4, 2);
ex.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"), 4,5);
}
ex.SetCellValue((j + 1).ToString(), row + j % rowcount, col);
ex.SetCellValue(datalist[j].sssbmc, row + j % rowcount, col + 1);
ex.SetCellValue(datalist[j].sssswz, row + j % rowcount, col + 2);
ex.SetCellValue(datalist[j].sssbbh, row + j % rowcount, col + 3);
ex.SetCellValue(datalist[j].statuts, row + j % rowcount, col + 4);
ex.SetCellValue(datalist[j].Remark, row + j % rowcount, col + 5);
//ex.SetCellValue(datalist[j].zrr, row + j % rowcount, col + 7);
}
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(string obj)
{
obj = "317";
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\10配电变压器汇总表.xls";
IList<PS_sheetTemp> list1 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=10 ");
IList<PS_sheetTemp> list2 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=20 ");
IList<PS_sheetTemp> list3 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=30 ");
IList<PS_sheetTemp> list4 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=50 ");
IList<PS_sheetTemp> list5 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=63 ");
IList<PS_sheetTemp> list6 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=80 ");
IList<PS_sheetTemp> list7 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=100 ");
IList<PS_sheetTemp> list8 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=125 ");
IList<PS_sheetTemp> list9 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=160 ");
IList<PS_sheetTemp> list10 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=200 ");
IList<PS_sheetTemp> list11 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=250 ");
IList<PS_sheetTemp> list12 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=300 ");
IList<PS_sheetTemp> list13 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=315 ");
IList<PS_sheetTemp> list14 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=400 ");
IList<PS_sheetTemp> list15 = Client.ClientHelper.PlatformSqlMap.GetList<PS_sheetTemp>("SelectPS_tqbyqByGDSView", "'" + obj + "' and d.byqCapcity=630 ");
ex.Open(fname);
//此处写填充内容代码
ex.SetCellValue(list1[0].Col1.ToString(), 7, 5);
ex.SetCellValue(list1[0].Col2.ToString(), 8, 5);
ex.SetCellValue(list1[0].Col3.ToString(), 9, 5);
ex.SetCellValue(list1[0].Col4.ToString(), 10, 5);
ex.SetCellValue(list1[0].Col5.ToString(), 11, 5);
ex.SetCellValue(list1[0].Col6.ToString(), 12, 5);
ex.SetCellValue(list1[0].Col7.ToString(), 13, 5);
ex.SetCellValue(list1[0].Col8.ToString(), 14, 5);
ex.SetCellValue(Convert.ToString(list1[0].Col1 + list1[0].Col3 + list1[0].Col5 + list1[0].Col7), 5, 5);
ex.SetCellValue(Convert.ToString(list1[0].Col2 + list1[0].Col4 + list1[0].Col6 + list1[0].Col8), 6, 5);
ex.SetCellValue(Convert.ToString(list1[0].Col1 + list2[0].Col1 + list3[0].Col1 + list4[0].Col1 + list5[0].Col1 + list6[0].Col1 + list7[0].Col1 + list8[0].Col1 + list9[0].Col1 + list10[0].Col1 + list11[0].Col1 + list12[0].Col1 + list13[0].Col1 + list14[0].Col1 + list15[0].Col1), 7, 4);
ex.SetCellValue(list2[0].Col1.ToString(), 7, 6);
ex.SetCellValue(list2[0].Col2.ToString(), 8, 6);
ex.SetCellValue(list2[0].Col3.ToString(), 9, 6);
ex.SetCellValue(list2[0].Col4.ToString(), 10, 6);
ex.SetCellValue(list2[0].Col5.ToString(), 11, 6);
ex.SetCellValue(list2[0].Col6.ToString(), 12, 6);
ex.SetCellValue(list2[0].Col7.ToString(), 13, 6);
ex.SetCellValue(list2[0].Col8.ToString(), 14, 6);
ex.SetCellValue(Convert.ToString(list2[0].Col1 + list2[0].Col3 + list2[0].Col5 + list2[0].Col7), 5, 6);
ex.SetCellValue(Convert.ToString(list2[0].Col2 + list2[0].Col4 + list2[0].Col6 + list2[0].Col8), 6, 6);
ex.SetCellValue(Convert.ToString(list1[0].Col2 + list2[0].Col2 + list3[0].Col2 + list4[0].Col2 + list5[0].Col2 + list6[0].Col2 + list7[0].Col2 + list8[0].Col2 + list9[0].Col2 + list10[0].Col2 + list11[0].Col2 + list12[0].Col2 + list13[0].Col2 + list14[0].Col2 + list15[0].Col2), 8, 4);
ex.SetCellValue(list3[0].Col1.ToString(), 7, 7);
ex.SetCellValue(list3[0].Col2.ToString(), 8, 7);
ex.SetCellValue(list3[0].Col3.ToString(), 9, 7);
ex.SetCellValue(list3[0].Col4.ToString(), 10, 7);
ex.SetCellValue(list3[0].Col5.ToString(), 11, 7);
ex.SetCellValue(list3[0].Col6.ToString(), 12, 7);
ex.SetCellValue(list3[0].Col7.ToString(), 13, 7);
ex.SetCellValue(list3[0].Col8.ToString(), 14, 7);
ex.SetCellValue(Convert.ToString(list3[0].Col1 + list3[0].Col3 + list3[0].Col5 + list3[0].Col7), 5, 7);
ex.SetCellValue(Convert.ToString(list3[0].Col2 + list3[0].Col4 + list3[0].Col6 + list3[0].Col8), 6, 7);
ex.SetCellValue(Convert.ToString(list1[0].Col3 + list2[0].Col3 + list3[0].Col3 + list4[0].Col3 + list5[0].Col3 + list6[0].Col3 + list7[0].Col3 + list8[0].Col3 + list9[0].Col3 + list10[0].Col3 + list11[0].Col3 + list12[0].Col3 + list13[0].Col3 + list14[0].Col3 + list15[0].Col3), 9, 4);
ex.SetCellValue(list4[0].Col1.ToString(), 7, 8);
ex.SetCellValue(list4[0].Col2.ToString(), 8, 8);
ex.SetCellValue(list4[0].Col3.ToString(), 9, 8);
ex.SetCellValue(list4[0].Col4.ToString(), 10, 8);
ex.SetCellValue(list4[0].Col5.ToString(), 11, 8);
ex.SetCellValue(list4[0].Col6.ToString(), 12, 8);
ex.SetCellValue(list4[0].Col7.ToString(), 13, 8);
ex.SetCellValue(list4[0].Col8.ToString(), 14, 8);
ex.SetCellValue(Convert.ToString(list4[0].Col1 + list4[0].Col3 + list4[0].Col5 + list4[0].Col7), 5, 8);
ex.SetCellValue(Convert.ToString(list4[0].Col2 + list4[0].Col4 + list4[0].Col6 + list4[0].Col8), 6, 8);
ex.SetCellValue(Convert.ToString(list1[0].Col4 + list2[0].Col4 + list3[0].Col4 + list4[0].Col4 + list5[0].Col4 + list6[0].Col4 + list7[0].Col4 + list8[0].Col4 + list9[0].Col4 + list10[0].Col4 + list11[0].Col4 + list12[0].Col4 + list13[0].Col4 + list14[0].Col4 + list15[0].Col4), 10, 4);
ex.SetCellValue(list5[0].Col1.ToString(), 7, 9);
ex.SetCellValue(list5[0].Col2.ToString(), 8, 9);
ex.SetCellValue(list5[0].Col3.ToString(), 9, 9);
ex.SetCellValue(list5[0].Col4.ToString(), 10, 9);
ex.SetCellValue(list5[0].Col5.ToString(), 11, 9);
ex.SetCellValue(list5[0].Col6.ToString(), 12, 9);
ex.SetCellValue(list5[0].Col7.ToString(), 13, 9);
ex.SetCellValue(list5[0].Col8.ToString(), 14, 9);
ex.SetCellValue(Convert.ToString(list5[0].Col1 + list5[0].Col3 + list5[0].Col5 + list5[0].Col7), 5, 9);
ex.SetCellValue(Convert.ToString(list5[0].Col2 + list5[0].Col4 + list5[0].Col6 + list5[0].Col8), 6, 9);
ex.SetCellValue(Convert.ToString(list1[0].Col5 + list2[0].Col5 + list3[0].Col5 + list4[0].Col5 + list5[0].Col4 + list6[0].Col5 + list7[0].Col5 + list8[0].Col5 + list9[0].Col5 + list10[0].Col5 + list11[0].Col5 + list12[0].Col5 + list13[0].Col5 + list14[0].Col5 + list15[0].Col5), 11, 4);
ex.SetCellValue(list6[0].Col1.ToString(), 7, 10);
ex.SetCellValue(list6[0].Col2.ToString(), 8, 10);
ex.SetCellValue(list6[0].Col3.ToString(), 9, 10);
ex.SetCellValue(list6[0].Col4.ToString(), 10, 10);
ex.SetCellValue(list6[0].Col5.ToString(), 11, 10);
ex.SetCellValue(list6[0].Col6.ToString(), 12, 10);
ex.SetCellValue(list6[0].Col7.ToString(), 13, 10);
ex.SetCellValue(list6[0].Col8.ToString(), 14, 10);
ex.SetCellValue(Convert.ToString(list6[0].Col1 + list6[0].Col3 + list6[0].Col5 + list6[0].Col7), 5, 10);
ex.SetCellValue(Convert.ToString(list6[0].Col2 + list6[0].Col4 + list6[0].Col6 + list6[0].Col8), 6, 10);
ex.SetCellValue(Convert.ToString(list1[0].Col6 + list2[0].Col6 + list3[0].Col6 + list4[0].Col6 + list5[0].Col6 + list6[0].Col6 + list7[0].Col6 + list8[0].Col6 + list9[0].Col6 + list10[0].Col6 + list11[0].Col6 + list12[0].Col6 + list13[0].Col6 + list14[0].Col6 + list15[0].Col6), 12, 4);
ex.SetCellValue(list7[0].Col1.ToString(), 7, 11);
ex.SetCellValue(list7[0].Col2.ToString(), 8, 11);
ex.SetCellValue(list7[0].Col3.ToString(), 9, 11);
ex.SetCellValue(list7[0].Col4.ToString(), 10, 11);
ex.SetCellValue(list7[0].Col5.ToString(), 11, 11);
ex.SetCellValue(list7[0].Col6.ToString(), 12, 11);
ex.SetCellValue(list7[0].Col7.ToString(), 13, 11);
ex.SetCellValue(list7[0].Col8.ToString(), 14, 11);
ex.SetCellValue(Convert.ToString(list7[0].Col1 + list7[0].Col3 + list7[0].Col5 + list7[0].Col7), 5, 11);
ex.SetCellValue(Convert.ToString(list7[0].Col2 + list7[0].Col4 + list7[0].Col6 + list7[0].Col8), 6, 11);
ex.SetCellValue(Convert.ToString(list1[0].Col7 + list2[0].Col7 + list3[0].Col7 + list4[0].Col7 + list5[0].Col7 + list6[0].Col7 + list7[0].Col7 + list8[0].Col7 + list9[0].Col7 + list10[0].Col7 + list11[0].Col7 + list12[0].Col7 + list13[0].Col7 + list14[0].Col7 + list15[0].Col7), 13, 4);
ex.SetCellValue(list8[0].Col1.ToString(), 7, 12);
ex.SetCellValue(list8[0].Col2.ToString(), 8, 12);
ex.SetCellValue(list8[0].Col3.ToString(), 9, 12);
ex.SetCellValue(list8[0].Col4.ToString(), 10, 12);
ex.SetCellValue(list8[0].Col5.ToString(), 11, 12);
ex.SetCellValue(list8[0].Col6.ToString(), 12, 12);
ex.SetCellValue(list8[0].Col7.ToString(), 13, 12);
ex.SetCellValue(list8[0].Col8.ToString(), 14, 12);
ex.SetCellValue(Convert.ToString(list8[0].Col1 + list8[0].Col3 + list8[0].Col5 + list8[0].Col7), 5, 12);
ex.SetCellValue(Convert.ToString(list8[0].Col2 + list8[0].Col4 + list8[0].Col6 + list8[0].Col8), 6, 12);
ex.SetCellValue(Convert.ToString(list1[0].Col8 + list2[0].Col8 + list3[0].Col8 + list4[0].Col8 + list5[0].Col8 + list6[0].Col8 + list7[0].Col8 + list8[0].Col8 + list9[0].Col8 + list10[0].Col8 + list11[0].Col8 + list12[0].Col8 + list13[0].Col8 + list14[0].Col8 + list15[0].Col8), 14, 4);
ex.SetCellValue(list9[0].Col1.ToString(), 7, 13);
ex.SetCellValue(list9[0].Col2.ToString(), 8, 13);
ex.SetCellValue(list9[0].Col3.ToString(), 9, 13);
ex.SetCellValue(list9[0].Col4.ToString(), 10, 13);
ex.SetCellValue(list9[0].Col5.ToString(), 11, 13);
ex.SetCellValue(list9[0].Col6.ToString(), 12, 13);
ex.SetCellValue(list9[0].Col7.ToString(), 13, 13);
ex.SetCellValue(list9[0].Col8.ToString(), 14, 13);
ex.SetCellValue(Convert.ToString(list9[0].Col1 + list9[0].Col3 + list9[0].Col5 + list9[0].Col7), 5, 13);
ex.SetCellValue(Convert.ToString(list9[0].Col2 + list9[0].Col4 + list9[0].Col6 + list9[0].Col8), 6, 13);
ex.SetCellValue(list10[0].Col1.ToString(), 7, 14);
ex.SetCellValue(list10[0].Col2.ToString(), 8, 14);
ex.SetCellValue(list10[0].Col3.ToString(), 9, 14);
ex.SetCellValue(list10[0].Col4.ToString(), 10, 14);
ex.SetCellValue(list10[0].Col5.ToString(), 11, 14);
ex.SetCellValue(list10[0].Col6.ToString(), 12, 14);
ex.SetCellValue(list10[0].Col7.ToString(), 13, 14);
ex.SetCellValue(list10[0].Col8.ToString(), 14, 14);
ex.SetCellValue(Convert.ToString(list10[0].Col1 + list10[0].Col3 + list10[0].Col5 + list10[0].Col7), 5, 14);
ex.SetCellValue(Convert.ToString(list10[0].Col2 + list10[0].Col4 + list10[0].Col6 + list10[0].Col8), 6, 14);
ex.SetCellValue(list11[0].Col1.ToString(), 7, 15);
ex.SetCellValue(list11[0].Col2.ToString(), 8, 15);
ex.SetCellValue(list11[0].Col3.ToString(), 9, 15);
ex.SetCellValue(list11[0].Col4.ToString(), 10, 15);
ex.SetCellValue(list11[0].Col5.ToString(), 11, 15);
ex.SetCellValue(list11[0].Col6.ToString(), 12, 15);
ex.SetCellValue(list11[0].Col7.ToString(), 13, 15);
ex.SetCellValue(list11[0].Col8.ToString(), 14, 15);
ex.SetCellValue(Convert.ToString(list11[0].Col1 + list11[0].Col3 + list11[0].Col5 + list11[0].Col7), 5, 15);
ex.SetCellValue(Convert.ToString(list11[0].Col2 + list11[0].Col4 + list11[0].Col6 + list11[0].Col8), 6, 15);
ex.SetCellValue(list12[0].Col1.ToString(), 7, 16);
ex.SetCellValue(list12[0].Col2.ToString(), 8, 16);
ex.SetCellValue(list12[0].Col3.ToString(), 9, 16);
ex.SetCellValue(list12[0].Col4.ToString(), 10, 16);
ex.SetCellValue(list12[0].Col5.ToString(), 11, 16);
ex.SetCellValue(list12[0].Col6.ToString(), 12, 16);
ex.SetCellValue(list12[0].Col7.ToString(), 13, 16);
ex.SetCellValue(list12[0].Col8.ToString(), 14, 16);
ex.SetCellValue(Convert.ToString(list12[0].Col1 + list12[0].Col3 + list12[0].Col5 + list12[0].Col7), 5, 16);
ex.SetCellValue(Convert.ToString(list12[0].Col2 + list12[0].Col4 + list12[0].Col6 + list12[0].Col8), 6, 16);
ex.SetCellValue(list13[0].Col1.ToString(), 7, 17);
ex.SetCellValue(list13[0].Col2.ToString(), 8, 17);
ex.SetCellValue(list13[0].Col3.ToString(), 9, 17);
ex.SetCellValue(list13[0].Col4.ToString(), 10, 17);
ex.SetCellValue(list13[0].Col5.ToString(), 11, 17);
ex.SetCellValue(list13[0].Col6.ToString(), 12, 17);
ex.SetCellValue(list13[0].Col7.ToString(), 13, 17);
ex.SetCellValue(list13[0].Col8.ToString(), 14, 17);
ex.SetCellValue(Convert.ToString(list13[0].Col1 + list13[0].Col3 + list13[0].Col5 + list13[0].Col7), 5, 17);
ex.SetCellValue(Convert.ToString(list13[0].Col2 + list13[0].Col4 + list13[0].Col6 + list13[0].Col8), 6, 17);
ex.SetCellValue(list14[0].Col1.ToString(), 7, 18);
ex.SetCellValue(list14[0].Col2.ToString(), 8, 18);
ex.SetCellValue(list14[0].Col3.ToString(), 9, 18);
ex.SetCellValue(list14[0].Col4.ToString(), 10, 18);
ex.SetCellValue(list14[0].Col5.ToString(), 11, 18);
ex.SetCellValue(list14[0].Col6.ToString(), 12, 18);
ex.SetCellValue(list14[0].Col7.ToString(), 13, 18);
ex.SetCellValue(list14[0].Col8.ToString(), 14, 18);
ex.SetCellValue(Convert.ToString(list14[0].Col1 + list14[0].Col3 + list14[0].Col5 + list14[0].Col7), 5, 18);
ex.SetCellValue(Convert.ToString(list14[0].Col2 + list14[0].Col4 + list14[0].Col6 + list14[0].Col8), 6, 18);
ex.SetCellValue(list15[0].Col1.ToString(), 7, 19);
ex.SetCellValue(list15[0].Col2.ToString(), 8, 19);
ex.SetCellValue(list15[0].Col3.ToString(), 9, 19);
ex.SetCellValue(list15[0].Col4.ToString(), 10, 19);
ex.SetCellValue(list15[0].Col5.ToString(), 11, 19);
ex.SetCellValue(list15[0].Col6.ToString(), 12, 19);
ex.SetCellValue(list15[0].Col7.ToString(), 13, 19);
ex.SetCellValue(list15[0].Col8.ToString(), 14, 19);
ex.SetCellValue(Convert.ToString(list15[0].Col1 + list15[0].Col3 + list15[0].Col5 + list15[0].Col7), 5, 19);
ex.SetCellValue(Convert.ToString(list15[0].Col2 + list15[0].Col4 + list15[0].Col6 + list15[0].Col8), 6, 19);
ex.SetCellValue(Convert.ToString(list1[0].Col1 + list1[0].Col3 + list1[0].Col5 + list1[0].Col7 + list2[0].Col1 + list2[0].Col3 + list2[0].Col5 + list2[0].Col7 + list3[0].Col1 + list3[0].Col3 + list3[0].Col5 + list3[0].Col7 + list4[0].Col1 + list4[0].Col3 + list4[0].Col5 + list4[0].Col7 + list5[0].Col1 + list5[0].Col3 + list5[0].Col5 + list5[0].Col7 + list6[0].Col1 + list6[0].Col3 + list6[0].Col5 + list6[0].Col7 + list7[0].Col1 + list7[0].Col3 + list7[0].Col5 + list7[0].Col7 + list8[0].Col1 + list8[0].Col3 + list8[0].Col5 + list8[0].Col7 + list9[0].Col1 + list9[0].Col3 + list9[0].Col5 + list9[0].Col7 + list10[0].Col1 + list10[0].Col3 + list10[0].Col5 + list10[0].Col7 + list11[0].Col1 + list11[0].Col3 + list11[0].Col5 + list11[0].Col7 + list12[0].Col1 + list12[0].Col3 + list12[0].Col5 + list12[0].Col7 + list13[0].Col1 + list13[0].Col3 + list13[0].Col5 + list13[0].Col7 + list14[0].Col1 + list14[0].Col3 + list14[0].Col5 + list14[0].Col7 + list15[0].Col1 + list15[0].Col3 + list15[0].Col5 + list15[0].Col7), 5, 4);
ex.SetCellValue(Convert.ToString(list1[0].Col2 + list1[0].Col4 + list1[0].Col6 + list1[0].Col8 + list2[0].Col2 + list2[0].Col4 + list2[0].Col6 + list2[0].Col8 + list3[0].Col2 + list3[0].Col4 + list3[0].Col6 + list3[0].Col8 + list4[0].Col2 + list4[0].Col4 + list4[0].Col6 + list4[0].Col8 + list5[0].Col2 + list5[0].Col4 + list5[0].Col6 + list5[0].Col8 + list6[0].Col2 + list6[0].Col4 + list6[0].Col6 + list6[0].Col8 + list7[0].Col2 + list7[0].Col4 + list7[0].Col6 + list7[0].Col8 + list8[0].Col2 + list8[0].Col4 + list8[0].Col6 + list8[0].Col8 + list9[0].Col2 + list9[0].Col4 + list9[0].Col6 + list9[0].Col8 + list10[0].Col2 + list10[0].Col4 + list10[0].Col6 + list10[0].Col8 + list11[0].Col2 + list11[0].Col4 + list11[0].Col6 + list11[0].Col8 + list12[0].Col2 + list12[0].Col4 + list12[0].Col6 + list12[0].Col8 + list13[0].Col2 + list13[0].Col4 + list13[0].Col6 + list13[0].Col8 + list14[0].Col2 + list14[0].Col4 + list14[0].Col6 + list14[0].Col8 + list15[0].Col2 + list15[0].Col4 + list15[0].Col6 + list15[0].Col8), 6, 4);
ex.ShowExcel();
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(PS_tqdlbh jl) {
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\13剩余电流动作保护器测试记录.xls";
ex.Open(fname);
int row = 1;
int col = 1;
//测量记录
IList<PJ_13dlbhjl> list = Client.ClientHelper.PlatformSqlMap.GetListByWhere<PJ_13dlbhjl>(" where sbID='" + jl.sbID + "' order by CreateDate");
int p = Ecommon.GetPagecount(list.Count, 12);
for (int i = 0; i < p - 1; i++) {
ex.CopySheet(1, 1 + i);
}
ex.ActiveSheet(1);
//线路名称行
ex.SetCellValue(jl.tqName, 4, 1);
ex.SetCellValue(jl.Factory, 5, 13);
ex.SetCellValue(jl.InstallAdress, row + 4, col + 3);
ex.SetCellValue(jl.dzdl, row + 4, col + 6);
ex.SetCellValue(jl.sbModle, row + 4, col + 10);
ex.SetCellValue(jl.Factory, row + 4, col + 13);
//设备名称行
ex.SetCellValue(jl.glr, row + 6, col + 3);
ex.SetCellValue(jl.dzsj, row + 6, col + 6);
ex.SetCellValue(jl.InDate.Year.ToString(), row + 7, col + 10);
ex.SetCellValue(jl.InDate.Month.ToString(), row + 7, col + 11);
ex.SetCellValue(jl.InDate.Day.ToString(), row + 7, col + 13);
for (int page = 1; page <= p; page++) {
ex.ActiveSheet(page);
if (page == 1) {
for (int i = 0; i < 12; i++) {
if (i + (page - 1) * 12 < list.Count) {
PJ_13dlbhjl obj = list[i + (page - 1) * 12];
ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col);
ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1);
ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2);
ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3);
ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4);
ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5);
ex.SetCellValue(obj.csr, row + 11 + i, col + 6);
}
}
} else {
for (int i = 0; i < 12; i++) {
if (i + (page - 1) * 12 < list.Count) {
PJ_13dlbhjl obj = list[i + (page - 1) * 12];
ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col);
ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1);
ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2);
ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3);
ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4);
ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5);
ex.SetCellValue(obj.csr, row + 11 + i, col + 6);
}
}
}
}
ex.ActiveSheet(1);
ex.ShowExcel();
}
/// <summary>
/// 设置Excel数据信息
/// </summary>
/// <param name="ex"></param>
/// <param name="list"></param>
/// <param name="caplist"></param>
/// <param name="modtmlist"></param>
/// <param name="modmflist"></param>
/// <param name="jstrfilter"></param>
/// <param name="strfilter"></param>
/// <param name="jstart"></param>
/// <param name="j"></param>
/// <param name="jmax"></param>
/// <param name="istart"></param>
/// <param name="istart2"></param>
public static void setExceldt(ExcelAccess ex, IList list, IList caplist, IList modtmlist, IList modmflist, string jstrfilter, string strfilter, int jstart, int j, int jmax, int istart, int istart2)
{
//string str = "";
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select b.byqCapcity from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter);
if (list.Count > 0) ex.SetCellValue(list.Count.ToString(), 5, jstart + j % jmax);
list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneInt", "select sum(b.byqCapcity) from dbo.mOrg a,dbo.PS_tqbyq b,dbo.PS_xl c, dbo.PS_tq d where a.OrgCode=c.OrgCode and c.LineCode=d.xlCode and d.tqID=b.tqID " + strfilter + jstrfilter);
//if (list[0] == null) list[0] = 0;
if (list[0] != null)
ex.SetCellValue(list[0].ToString(), 6, jstart + j % jmax);
for (int itm = 0; itm < modtmlist.Count; itm++)
{
ex.ActiveSheet("Sheet" + (1 + (itm / 6) * (int)Math.Ceiling(caplist.Count /( jmax + 0.0)) + j / jmax));
if (j % jmax == 0)
{
ex.SetCellValue(modtmlist[itm].ToString(), istart + (itm % 6) * 2, 1);
//jstrfilter = " and 1=1";
//setExceltmdt(ex, list, modtmlist, jstrfilter, strfilter, jstart, j, jmax, istart, itm);
}
//jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'";
setExceltmdt(ex, list, modtmlist, jstrfilter, strfilter, jstart, j, jmax, istart, itm);
}
for (int imf = 0; imf < modmflist.Count; imf++)
{
ex.ActiveSheet("Sheet" + (1 + (imf / 3) * (int)Math.Ceiling(caplist.Count / (jmax + 0.0)) + j / jmax));
if (j % jmax == 0)
{
ex.SetCellValue(modmflist[imf].ToString(), istart2 + (imf % 3) * 2, 2);
//jstrfilter = " and 1=1";
//setExcelmfdt(ex, list, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart2, imf);
}
//jstrfilter = " and 1=1 and b.byqCapcity='" + caplist[j] + "'";
setExcelmfdt(ex, list, modmflist, jstrfilter, strfilter, jstart, j, jmax, istart2, imf);
}
}
/// <summary>
/// 文档格式预定义好的，生成台账
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel2(IList<PS_tqdlbh> list, string orgname) {
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\13剩余电流动作保护器台帐.xls";
ex.Open(fname);
int row = 1;
int col = 1;
//测量记录
int p = Ecommon.GetPagecount(list.Count, 20);
for (int i = 0; i < p - 1; i++) {
ex.CopySheet(1, 1 + i);
}
ex.ActiveSheet(1);
//单位名称
ex.SetCellValue(orgname, 3, 3);
for (int page = 1; page <= p; page++) {
ex.ActiveSheet(page);
if (page == 1) {
for (int i = 0; i < 20; i++) {
if (i + (page - 1) * 20 < list.Count) {
PS_tqdlbh jl = list[i + (page - 1) * 20];
//ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col);
//ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1);
//ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2);
//ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3);
//ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4);
//ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5);
//ex.SetCellValue(obj.csr, row + 11 + i, col + 6);
ex.SetCellValue(jl.tqName, row + 5 + i, col + 2);
ex.SetCellValue(jl.sbModle, row + 5 + i, col + 3);
ex.SetCellValue(jl.Factory, row + 5 + i, col + 4);
ex.SetCellValue(jl.dzdl, row + 5 + i, col + 5);
ex.SetCellValue(jl.dzsj, row + 5 + i, col + 6);
ex.SetCellValue(jl.InDate.Year.ToString(), row + 5 + i, col + 7);
ex.SetCellValue(jl.InDate.Month.ToString(), row + 5 + i, col + 8);
ex.SetCellValue(jl.InDate.Day.ToString(), row + 5 + i, col + 9);
}
}
} else {
for (int i = 0; i < 20; i++) {
if (i + (page - 1) * 12 < list.Count) {
PS_tqdlbh jl = list[i + (page - 1) * 20];
//ex.SetCellValue(obj.rq.Year.ToString(), row + 11 + i, col);
//ex.SetCellValue(obj.rq.Month.ToString(), row + 11 + i, col + 1);
//ex.SetCellValue(obj.rq.Day.ToString(), row + 11 + i, col + 2);
//ex.SetCellValue(obj.dzdl, row + 11 + i, col + 3);
//ex.SetCellValue(obj.dzsj.ToString(), row + 11 + i, col + 4);
//ex.SetCellValue(obj.yxqk, row + 11 + i, col + 5);
//ex.SetCellValue(obj.csr, row + 11 + i, col + 6);
ex.SetCellValue(jl.tqName, row + 5 + i, col + 2);
ex.SetCellValue(jl.sbModle, row + 5 + i, col + 3);
ex.SetCellValue(jl.Factory, row + 5 + i, col + 4);
ex.SetCellValue(jl.dzdl, row + 5 + i, col + 5);
ex.SetCellValue(jl.dzsj, row + 5 + i, col + 6);
ex.SetCellValue(jl.InDate.Year.ToString(), row + 5 + i, col + 7);
ex.SetCellValue(jl.InDate.Month.ToString(), row + 5 + i, col + 8);
ex.SetCellValue(jl.InDate.Day.ToString(), row + 5 + i, col + 9);
}
}
}
}
ex.ActiveSheet(1);
ex.ShowExcel();
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(IList<sdjl_07jdzz> objlist) {
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\送电08接地装置检测.xls";
ex.Open(fname);
//此处写填充内容代码
int rowcount = 6;
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(objlist.Count, 16))
{
pageindex = Ecommon.GetPagecount(objlist.Count, 16);
}
for (int j = 1; j <= pageindex; j++)
{
ex.SetCellValue(objlist[0].LineName, 4, 3);
if (j > 1)
{
ex.CopySheet(1, 1);
}
}
for (int j = 1; j <= pageindex; j++)
{
ex.ActiveSheet(j);
ex.ReNameWorkSheet(j, "Sheet" + (j));
int prepageindex = j - 1;
//主题
int starow = prepageindex * 16 + 1;
int endrow = j * 16;
if (objlist.Count > endrow)
{
for (int i = 0; i < 16; i++)
{
ex.SetCellValue(objlist[starow - 1 + i].gth, rowcount + 1 + i, 1);
ex.SetCellValue(objlist[starow - 1 + i].gzwz, rowcount + 1 + i, 2);
ex.SetCellValue(objlist[starow - 1 + i].sbmc, rowcount + 1 + i, 3);
ex.SetCellValue(objlist[starow - 1 + i].jddz.ToString(), rowcount + 1 + i, 4);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Year.ToString(), rowcount + 1 + i, 5);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Month.ToString(), rowcount + 1 + i, 6);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Day.ToString(), rowcount + 1 + i, 7);
ex.SetCellValue(objlist[starow - 1 + i].trdzr.ToString(), rowcount + 1 + i, 8);
ex.SetCellValue(objlist[starow - 1 + i].tz.ToString(), rowcount + 1 + i, 9);
ex.SetCellValue(objlist[starow - 1 + i].xhgg, rowcount + 1 + i, 10);
ex.SetCellValue(objlist[starow - 1 + i].fzxl, rowcount + 1 + i, 11);
ex.SetCellValue(objlist[starow - 1 + i].CreateMan, rowcount + 1 + i, 12);
}
}
else if (objlist.Count <= endrow && objlist.Count >= starow)
{
for (int i = 0; i < objlist.Count - starow + 1; i++)
{
ex.SetCellValue(objlist[starow - 1 + i].gth, rowcount + 1 + i, 1);
ex.SetCellValue(objlist[starow - 1 + i].gzwz, rowcount + 1 + i, 2);
ex.SetCellValue(objlist[starow - 1 + i].sbmc, rowcount + 1 + i, 3);
ex.SetCellValue(objlist[starow - 1 + i].jddz.ToString(), rowcount + 1 + i, 4);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Year.ToString(), rowcount + 1 + i, 5);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Month.ToString(), rowcount + 1 + i, 6);
ex.SetCellValue(objlist[starow - 1 + i].CreateDate.Day.ToString(), rowcount + 1 + i, 7);
ex.SetCellValue(objlist[starow - 1 + i].trdzr.ToString(), rowcount + 1 + i, 8);
ex.SetCellValue(objlist[starow - 1 + i].tz.ToString(), rowcount + 1 + i, 9);
ex.SetCellValue(objlist[starow - 1 + i].xhgg, rowcount + 1 + i, 10);
ex.SetCellValue(objlist[starow - 1 + i].fzxl, rowcount + 1 + i, 11);
ex.SetCellValue(objlist[starow - 1 + i].CreateMan, rowcount + 1 + i, 12);
//ex.SetCellValue(objlist[starow - 1 + i].Remark, rowcount + i, 10);
}
}
}
ex.ActiveSheet(1);
ex.ShowExcel();
}
void ContentChanged(Control ctrl) {
LP_Temple lp = (LP_Temple)ctrl.Tag;
string str = ctrl.Text;
if (dsoFramerWordControl1.MyExcel == null) {
return;
}
//Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook;
//Excel.Worksheet sheet;
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
if (lp.KindTable != "" && activeSheetName != lp.KindTable) {
if (lp.KindTable != "") {
activeSheetName = lp.KindTable;
sheet = wb.Application.Sheets[lp.KindTable] as Excel.Worksheet;
activeSheetIndex = sheet.Index;
} else {
sheet = wb.Application.Sheets[1] as Excel.Worksheet;
activeSheetIndex = sheet.Index;
activeSheetName = sheet.Name;
}
}
ea.ActiveSheet(activeSheetIndex);
unLockExcel(wb, sheet);
if (lp.CtrlType.Contains("uc_gridcontrol")) { FillTable(ea, lp, (ctrl as uc_gridcontrol).GetContent(String2Int(lp.WordCount.Split(pchar)))); return; } else if (lp.CtrlType.Contains("DevExpress.XtraEditors.DateEdit")) {
FillTime(ea, lp, (ctrl as DateEdit).DateTime);
return;
}
string[] arrCellpos = lp.CellPos.Split(pchar);
string[] arrtemp = lp.WordCount.Split(pchar);
arrCellpos = StringHelper.ReplaceEmpty(arrCellpos).Split(pchar);
arrtemp = StringHelper.ReplaceEmpty(arrtemp).Split(pchar);
List<int> arrCellCount = String2Int(arrtemp);
if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) {
ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + lp.CellPos)) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + lp.CellPos] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(lp.CellPos)[0];
tfv.YExcelPos = GetCellPos(lp.CellPos)[1];
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(lp.CellPos)[0];
tfv.YExcelPos = GetCellPos(lp.CellPos)[1];
valuehs.Add(lp.LPID + "$" + lp.CellPos, tfv);
}
} else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) {
StringHelper help = new StringHelper();
if (lp.CellName == "编号") {
for (int j = 0; j < arrCellpos.Length; j++) {
if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[j])) {
string strNew = str.Substring(0, (str.Length > 0 ? str.Length : 1) - 1) + (j + 1).ToString();
ea.SetCellValue("'" + strNew, GetCellPos(arrCellpos[j])[0], GetCellPos(arrCellpos[j])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[j])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[j]] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[j])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[j])[1];
tfv.ExcelSheetName = sheet.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[j])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[j])[1];
tfv.ExcelSheetName = sheet.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[j], tfv);
}
}
}
return;
}
int i = 0;
if (arrCellCount[0] != arrCellCount[1]) {
if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[0])) {
ea.SetCellValue("'" + str, GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = sheet.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = sheet.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv);
}
return;
}
ea.SetCellValue("'" + str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])),
GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue;
tfv.ControlValue = str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]));
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = sheet.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]));
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = sheet.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv);
}
str = str.Substring(help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") &&
str.IndexOf("\r\n") != -1 ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]));
i++;
}
str = help.GetPlitString(str, arrCellCount[1]);
FillMutilRows(ea, i, lp, str, arrCellCount, arrCellpos);
}
LockExcel(wb, sheet);
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(IList<PJ_21gzbxdh> objlist)
{
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\21电力故障电话接听记录.xls";
ex.Open(fname);
//此处写填充内容代码
int rowcount = 7;
//加页
int pageindex = 1;
if (pageindex < Ecommon.GetPagecount(objlist.Count, 19))
{
pageindex = Ecommon.GetPagecount(objlist.Count, 19);
}
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 * 19 + 1;
int endrow = j * 19;
if (objlist.Count > endrow)
{
for (int i = 0; i < 19; i++)
{
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, 9);
// ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i,12);
// ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 13);
}
}
else if (objlist.Count <= endrow && objlist.Count >= starow)
{
for (int i = 0; i < objlist.Count - starow + 1; i++)
{
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, 9);
// ex.SetCellValue(objlist[starow - 1 + i].djr, rowcount + i, 12);
// ex.SetCellValue(objlist[starow - 1 + i].clr, rowcount + i, 13);
}
}
}
ex.ActiveSheet(1);
string orgname="";
if (objlist.Count>0)
{
orgname = objlist[0].OrgName;
}
//变电所内容
ex.SetCellValue(orgname, 4, 5);
//记录
//for (int i = 0; i < objlist.Count;i++ )
//{
// ex.SetCellValue(objlist[i].rq.Month.ToString(), rowcount + i, 1);
// ex.SetCellValue(objlist[i].rq.Day.ToString(), rowcount + i, 2);
// ex.SetCellValue(objlist[i].rq.Hour.ToString(), rowcount + i, 3);
// ex.SetCellValue(objlist[i].rq.Minute.ToString(), rowcount + i, 4);
// ex.SetCellValue(objlist[i].lxfs, rowcount + i, 5);
// ex.SetCellValue(objlist[i].yhdz, rowcount + i, 6);
// ex.SetCellValue(objlist[i].gzjk, rowcount + i, 7);
// ex.SetCellValue(objlist[i].djr, rowcount + i,8);
// ex.SetCellValue(objlist[i].clr, rowcount + i,9);
//}
ex.ShowExcel();
}
void ctrl_Leave(object sender, EventArgs e) {
LP_Temple lp = (LP_Temple)(sender as Control).Tag;
string str = (sender as Control).Text;
if (dsoFramerWordControl1 == null || dsoFramerWordControl1.MyExcel == null) {
return;
}
try {
//Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook;
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
//Excel.Worksheet xx;
if (lp.KindTable != "" && activeSheetName != lp.KindTable) {
if (lp.KindTable != "") {
activeSheetName = lp.KindTable;
xx = wb.Application.Sheets[lp.KindTable] as Excel.Worksheet;
activeSheetIndex = xx.Index;
} else {
xx = wb.Application.Sheets[1] as Excel.Worksheet;
activeSheetIndex = xx.Index;
activeSheetName = xx.Name;
}
if (lp.KindTable != "") {
ea.ActiveSheet(lp.KindTable);
} else {
ea.ActiveSheet(1);
}
}
if (lp.CellPos == "") {
if (lp.CellName.IndexOf("绘图") > -1) {
//unLockExcel(wb, xx);
PJ_tbsj tb = MainHelper.PlatformSqlMap.GetOne<PJ_tbsj>("where picName = '" + str + "'");
if (tb != null) {
if (tb.picName != "表箱") {
string tempPath = Path.GetTempPath();
string tempfile = tempPath + "~" + Guid.NewGuid().ToString() + tb.S1;
FileStream fs;
fs = new FileStream(tempfile, FileMode.Create, FileAccess.Write);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(tb.picImage);
bw.Flush();
bw.Close();
fs.Close();
//IDataObject data = new DataObject(DataFormats.FileDrop, new string[] { tempfile });
//MemoryStream memo = new MemoryStream(4);
//byte[] bytes = new byte[] { (byte)(5), 0, 0, 0 };
//memo.Write(bytes, 0, bytes.Length);
//data.SetData("ttt", memo);
//Clipboard.SetDataObject(data);
Image im = Bitmap.FromFile(tempfile);
Bitmap bt = new Bitmap(im);
DataObject dataObject = new DataObject();
dataObject.SetData(DataFormats.Bitmap, bt);
Clipboard.SetDataObject(dataObject, true);
} else {
Microsoft.Office.Interop.Excel.Shape activShape = null;
activShape = xx.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal,
(float)153.75, (float)162.75, (float)22.5, (float)10.5);
activShape.TextFrame.Characters(1, 1).Font.Size = 8;
activShape.TextFrame.Characters(1, 1).Text = "1";
activShape.TextFrame.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
activShape.Cut();
}
}
} else {
if (valuehs.ContainsKey(lp.LPID)) {
WF_TableFieldValue tfv = valuehs[lp.LPID] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID, tfv);
}
}
return;
}
unLockExcel(wb, xx);
string[] arrCellpos = lp.CellPos.Split(pchar);
string[] arrtemp = lp.WordCount.Split(pchar);
arrCellpos = StringHelper.ReplaceEmpty(arrCellpos).Split(pchar);
if (lp.CtrlType.Contains("uc_gridcontrol")) {
FillTable(ea, lp, (sender as uc_gridcontrol).GetContent(String2Int(lp.WordCount.Split(pchar))));
LockExcel(wb, xx);
return;
} else if (lp.CtrlType.Contains("DevExpress.XtraEditors.DateEdit")) {
FillTime(ea, lp, (sender as DateEdit).DateTime);
LockExcel(wb, xx);
return;
} else if (lp.CtrlType.Contains("DevExpress.XtraEditors.SpinEdit")) {
IList<string> strList = new List<string>();
if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) {
if (string.IsNullOrEmpty(str))
ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
else
ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv);
}
} else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) {
int i = 0;
if (string.IsNullOrEmpty(str))
ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
else
ea.SetCellValue("'" + str, GetCellPos(arrCellpos[i])[0], GetCellPos(arrCellpos[i])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[i])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[i]] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[i])[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[i])[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[i], tfv);
}
}
LockExcel(wb, xx);
return;
}
string[] extraWord = lp.ExtraWord.Split(pchar);
List<int> arrCellCount = String2Int(arrtemp);
if (arrCellpos.Length == 1 || string.IsNullOrEmpty(arrCellpos[1])) {
if (string.IsNullOrEmpty(str))
ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
else
ea.SetCellValue("'" + str, GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + lp.CellPos)) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + lp.CellPos] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(lp.CellPos)[0];
tfv.YExcelPos = GetCellPos(lp.CellPos)[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(lp.CellPos)[0];
tfv.YExcelPos = GetCellPos(lp.CellPos)[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + lp.CellPos, tfv);
}
} else if (arrCellpos.Length > 1 && (!string.IsNullOrEmpty(arrCellpos[1]))) {
StringHelper help = new StringHelper();
if (lp.CellName == "编号") {
for (int j = 0; j < arrCellpos.Length; j++) {
if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[j]) && str != "") {
string strNew = str.Substring(0, str.Length - 1) + (j + 1).ToString();
ea.SetCellValue("'" + strNew, GetCellPos(arrCellpos[j])[0], GetCellPos(arrCellpos[j])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[j])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[j]] as WF_TableFieldValue;
tfv.ControlValue = strNew;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[j])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[j])[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = strNew;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[j])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[j])[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[j], tfv);
}
}
}
LockExcel(wb, xx);
return;
}
int i = 0;
if (arrCellCount[0] != arrCellCount[1]) {
if (str.IndexOf("\r\n") == -1 && str.Length <= help.GetFristLen(str, arrCellCount[0])) {
if (string.IsNullOrEmpty(str))
ea.SetCellValue("", GetCellPos(lp.CellPos)[0], GetCellPos(lp.CellPos)[1]);
else
ea.SetCellValue("'" + str, GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue;
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = str;
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv);
}
LockExcel(wb, xx);
return;
}
ea.SetCellValue("'" + str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])),
GetCellPos(arrCellpos[0])[0], GetCellPos(arrCellpos[0])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellpos[0])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellpos[0]] as WF_TableFieldValue;
tfv.ControlValue = (str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])));
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = (str.Substring(0, str.IndexOf("\r\n") != -1 && help.GetFristLen(str, arrCellCount[0]) >=
str.IndexOf("\r\n") ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0])));
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellpos[0])[0];
tfv.YExcelPos = GetCellPos(arrCellpos[0])[1];
tfv.ExcelSheetName = xx.Name;
valuehs.Add(lp.LPID + "$" + arrCellpos[0], tfv);
}
str = str.Substring(help.GetFristLen(str, arrCellCount[0]) >= str.IndexOf("\r\n") &&
str.IndexOf("\r\n") != -1 ? str.IndexOf("\r\n") : help.GetFristLen(str, arrCellCount[0]));
i++;
}
str = help.GetPlitString(str, arrCellCount[1]);
FillMutilRows(ea, i, lp, str, arrCellCount, arrCellpos);
}
if (lp.CellName == "单位") {
IList list = Client.ClientHelper.PlatformSqlMap.GetList("SelectOneStr", "select OrgCode from mOrg where OrgName='" + str + "'");
if (list.Count > 0) {
switch (kind) {
case "电力线路第一种工作票":
case "yzgzp":
strNumber = "07" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2);
break;
case "电力线路第二种工作票":
case "ezgzp":
strNumber = "08" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2);
break;
case "电力线路倒闸操作票":
case "dzczp":
strNumber = "BJ" + System.DateTime.Now.Year.ToString();
break;
case "电力线路事故应急抢修单":
case "xlqxp":
strNumber = list[0].ToString().Substring(list[0].ToString().Length - 2, 2) + System.DateTime.Now.Year.ToString();
break;
default:
strNumber = "07" + System.DateTime.Now.Year.ToString() + list[0].ToString().Substring(list[0].ToString().Length - 2, 2);
break;
}
IList<LP_Record> listLPRecord = ClientHelper.PlatformSqlMap.GetList<LP_Record>("SelectLP_RecordList", " where kind = '" + kind + "' and number like '" + strNumber + "%'");
if (kind == "yzgzp") {
strNumber += (listLPRecord.Count + 1).ToString().PadLeft(3, '0') + "-1";
} else {
strNumber += (listLPRecord.Count + 1).ToString().PadLeft(3, '0');
}
if (ctrlNumber != null) {
ctrlNumber.Text = strNumber;
}
if (currRecord != null) currRecord.OrgName = str;
//ContentChanged(ctrlNumber);
}
}
LockExcel(wb, xx);
} catch { }
}
/// <summary>
///创建工作表并将数据加入到工作表中
/// </summary>
/// <param name="ex">操作EXCEL表</param>
/// <param name="bdzlist">填入的数据组</param>
/// <param name="hs">每一个对应的行数</param>
/// <param name="stawz">每一个对应的要填写的位置</param>
/// <param name="pageindex">分的工作表的数目</param>
/// <returns>返回List<string> </returns>
public static void CreatandWritesheet(ExcelAccess ex,List<string>[] bdzlist,int[] hs,int[] stawz )
{
int pageindex = 1;
for (int i = 0; i < bdzlist.Length;i++ )
{
if (pageindex < GetPagecount(bdzlist[i].Count,hs[i]))
{
pageindex = GetPagecount(bdzlist[i].Count, hs[i]);
}
}
for (int j = 1; j <= pageindex; j++)
{
if (j > 1)
{
ex.CopySheet(0, j + 1);
ex.ActiveSheet(j + 1);
}
int prepageindex = j - 1;
//主题
int starow = prepageindex * 2 + 1;
int endrow = j * 2;
for (int num = 0; num < bdzlist.Length;num++ )
{
if (bdzlist[num].Count > endrow)
{
for (int i = 0; i < hs[num]; i++)
{
ex.SetCellValue(bdzlist[num][starow - 1 + i], stawz[num] + i, 1);
}
}
else if (bdzlist[num].Count <= endrow && bdzlist[num].Count >= starow)
{
for (int i = 0; i < bdzlist[num].Count - starow + 1; i++)
{
ex.SetCellValue(bdzlist[num][starow - 1 + i], stawz[num] + i, 1);
}
}
}
}
}
public void FillMutilRows(ExcelAccess ea, int i, LP_Temple lp, string str, List<int> arrCellCount, string[] arrCellPos) {
StringHelper help = new StringHelper();
str = help.GetPlitString(str, arrCellCount[1]);
string[] extraWord = lp.ExtraWord.Split(pchar);
string[] arrRst = str.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
int j = 0;
for (; i < arrCellPos.Length; i++) {
if (j >= arrRst.Length)
break;
ea.SetCellValue("'" + arrRst[j], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue;
tfv.ControlValue = arrRst[j];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = arrRst[j];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv);
}
j++;
}
}
private void btExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (MainHelper.UserOrg == null) return;
if (gridView1.FocusedRowHandle < 0) return;
DataRow dr = gridView1.GetDataRow(gridView1.FocusedRowHandle);
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fname = saveFileDialog1.FileName;
try
{
string slqwhere = " where RecordId='" + dr["recordID"]
+ "' and UserControlId='" + Temple.LPID
+ "' and WorkflowId='" + modleGuid
+ "' and WorkFlowInsId='" + parentObj.OrgName + "' ";
IList<WF_TableFieldValue> tfvli = MainHelper.PlatformSqlMap.GetList<WF_TableFieldValue>(slqwhere);
DSOFramerControl dsoFramerWordControl1 = new DSOFramerControl();
dsoFramerWordControl1.FileDataGzip = Temple.DocContent;
Excel.Workbook wb = dsoFramerWordControl1.AxFramerControl.ActiveDocument as Excel.Workbook;
Excel.Worksheet xx;
ExcelAccess ea = new ExcelAccess();
ea.MyWorkBook = wb;
ea.MyExcel = wb.Application;
string activeSheetName = "";
xx = wb.Application.Sheets[1] as Excel.Worksheet;
int i = 0;
ArrayList al = new ArrayList();
for (i = 1; i <= wb.Application.Sheets.Count; i++)
{
xx = wb.Application.Sheets[i] as Excel.Worksheet;
if (!al.Contains(xx.Name)) al.Add(xx.Name);
}
for (i = 0; i < tfvli.Count; i++)
{
if (!al.Contains(tfvli[i].ExcelSheetName))
{
continue;
}
LP_Temple field = MainHelper.PlatformSqlMap.GetOneByKey<LP_Temple>(tfvli[i].FieldId);
if (field != null)
{
if (field.isExplorer == 1)
{
continue;
}
}
if (activeSheetName != tfvli[i].ExcelSheetName)
{
if (activeSheetName != "")
{
xx = wb.Application.Sheets[activeSheetName] as Excel.Worksheet;
}
xx = wb.Application.Sheets[tfvli[i].ExcelSheetName] as Excel.Worksheet;
activeSheetName = tfvli[i].ExcelSheetName;
ea.ActiveSheet(xx.Index);
}
if (tfvli[i].XExcelPos > -1 && tfvli[i].YExcelPos > -1) ea.SetCellValue(tfvli[i].ControlValue, tfvli[i].XExcelPos, tfvli[i].YExcelPos);
}
dsoFramerWordControl1.FileDataGzip = Temple.DocContent;
dsoFramerWordControl1.FileSave(fname, true);
dsoFramerWordControl1.FileClose();
if (MsgBox.ShowAskMessageBox("导出成功，是否打开该文档？") != DialogResult.OK)
return;
System.Diagnostics.Process.Start(fname);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
MsgBox.ShowWarningMessageBox("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
return;
}
}
}
public void FillTime(ExcelAccess ea, LP_Temple lp, DateTime dt) {
string[] arrCellPos = lp.CellPos.Split(pchar);
arrCellPos = StringHelper.ReplaceEmpty(arrCellPos).Split(pchar);
string[] extraWord = lp.ExtraWord.Split(pchar);
IList<string> strList = new List<string>();
//if (arrCellPos.Length == 5)
//{
// strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString());
// strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString());
//}
//else if (arrCellPos.Length == 4)
//{
// strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString());
//}
//else if (arrCellPos.Length == 3)
//{
// // strList.Add(dt.Year.ToString()); strList.Add(dt.Month.ToString());
// //strList.Add(dt.Day.ToString()); strList.Add(dt.Hour.ToString()); strList.Add(dt.Minute.ToString());
// strList.Add(dt.Year.ToString());
// strList.Add(dt.Month.ToString());
// strList.Add(dt.Day.ToString());
//}
//else if (arrCellPos.Length == 2)
//{
// strList.Add(dt.Hour.ToString());
// strList.Add(dt.Minute.ToString());
//}
//else if (arrCellPos.Length == 1)
//{
// strList.Add(dt.ToString());
//}
switch (lp.WordCount) {
case "yyyy-MM-dd":
strList.Add(dt.Year.ToString());
strList.Add(dt.Month.ToString());
strList.Add(dt.Day.ToString());
break;
case "MM-dd日":
strList.Add(dt.Month.ToString());
strList.Add(dt.Day.ToString());
break;
case "yyyy-MM-dd HH:mm:ss":
strList.Add(dt.Year.ToString());
strList.Add(dt.Month.ToString());
strList.Add(dt.Day.ToString());
strList.Add(dt.Hour.ToString());
strList.Add(dt.Second.ToString());
break;
case "HH:mm:ss":
strList.Add(dt.Hour.ToString());
strList.Add(dt.Minute.ToString());
strList.Add(dt.Second.ToString());
break;
case "MM-dd日 HH:mm":
strList.Add(dt.Month.ToString());
strList.Add(dt.Day.ToString());
strList.Add(dt.Hour.ToString());
strList.Add(dt.Minute.ToString());
break;
case "dd日 HH:mm":
strList.Add(dt.Day.ToString());
strList.Add(dt.Hour.ToString());
strList.Add(dt.Minute.ToString());
break;
case "HH:mm":
strList.Add(dt.Hour.ToString());
strList.Add(dt.Minute.ToString());
break;
default:
strList.Add(dt.Year.ToString());
strList.Add(dt.Month.ToString());
strList.Add(dt.Day.ToString());
strList.Add(dt.Hour.ToString());
strList.Add(dt.Minute.ToString());
break;
}
// int i = 0;
for (int i = 0; i < strList.Count; i++) {
if (extraWord.Length > i) {
ea.SetCellValue("'" + strList[i] + extraWord[i], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue;
tfv.ControlValue = strList[i] + extraWord[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
tfv = valuehs[lp.LPID + "$" + arrCellPos[i] + "时间"] as WF_TableFieldValue;
if (tfv != null) {
tfv.ControlValue = dt.ToString();
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "完整时间";
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
tfv.ExcelSheetName = activeSheetName;
}
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = strList[i] + extraWord[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv);
tfv = new WF_TableFieldValue();
tfv.FieldId = lp.LPID;
tfv.ControlValue = dt.ToString();
tfv.FieldName = lp.CellName + "完整时间";
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + arrCellPos[i] + "完整时间", tfv);
}
} else {
ea.SetCellValue("'" + strList[i], GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + arrCellPos[i])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + arrCellPos[i]] as WF_TableFieldValue;
tfv.ControlValue = strList[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
tfv = valuehs[lp.LPID + "$" + arrCellPos[0] + "时间"] as WF_TableFieldValue;
if (tfv != null) {
tfv.ControlValue = dt.ToString();
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "完整时间";
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
tfv.ExcelSheetName = activeSheetName;
}
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = strList[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName;
tfv.XExcelPos = GetCellPos(arrCellPos[i])[0];
tfv.YExcelPos = GetCellPos(arrCellPos[i])[1];
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + arrCellPos[i], tfv);
tfv = new WF_TableFieldValue();
tfv.FieldId = lp.LPID;
tfv.ControlValue = dt.ToString();
tfv.FieldName = lp.CellName + "完整时间";
tfv.XExcelPos = -1;
tfv.YExcelPos = -1;
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + arrCellPos[0] + "完整时间", tfv);
}
}
}
//foreach (string str in strList)
//{
// ea.SetCellValue(str, GetCellPos(arrCellPos[i])[0], GetCellPos(arrCellPos[i])[1]);
// i++;
//}
}
/// <summary>
/// 文档格式预定义好的，只填写内容
/// </summary>
/// <param name="obj"></param>
public static void ExportExcel(PS_tqbyq obj)
{
//lgm
//页数
int pagecount =1;
ExcelAccess ex = new ExcelAccess();
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = Application.StartupPath + "\\00记录模板\\11配电变压器卡片.xls";
ex.Open(fname);
//此处写填充内容代码
int row = 1;
string strwhere1=" where byqID='"+obj.byqID+"'";
IList<PJ_11byqbd> pjbdlist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqbd>("SelectPJ_11byqbdList", strwhere1);
IList<PJ_11byqdydl> pjdydllist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqdydl>("SelectPJ_11byqdydlList", strwhere1);
IList<PJ_11byqdzcl> pjdzcllist = Client.ClientHelper.PlatformSqlMap.GetList<PJ_11byqdzcl>("SelectPJ_11byqdzclList", strwhere1);
if (pjbdlist.Count == 0) {
pjbdlist.Add(new PJ_11byqbd() { azrq = obj.byqInstallDate, azdd = obj.byqInstallAdress });
}
//计算页数
int byqhdpage = Ecommon.GetPagecount(pjbdlist.Count, 3);
if (byqhdpage > pagecount)
{
pagecount = byqhdpage;
}
int byqdypage = Ecommon.GetPagecount(pjdydllist.Count, 22);
if (byqdypage > pagecount)
{
pagecount = byqdypage;
}
int byqdzpage = Ecommon.GetPagecount(pjdzcllist.Count, 9);
if (byqdzpage > pagecount)
{
pagecount = byqdzpage;
}
//复制空模版
if (pagecount>1)
{
for (int i = 1; i < pagecount; i++)
{
ex.CopySheet(1, i);
ex.ReNameWorkSheet(i + 1, "Sheet" + (i + 1));
}
}
PS_tq temptq = Client.ClientHelper.PlatformSqlMap.GetOneByKey<PS_tq>(obj.tqID);
//固定内容
ex.ActiveSheet(1);
//变压器内容
row = 4;
ex.SetCellValue(obj.byqModle, row, 4);
ex.SetCellValue(obj.byqFactory, row, 14);
ex.SetCellValue(obj.byqVolOne + "KV", row, 23);
row++;
ex.SetCellValue(obj.byqPhase, row, 4);
ex.SetCellValue(obj.byqNumber, row, 14);
ex.SetCellValue(obj.byqVolTwo + "KV", row, 23);
row++;//6
ex.SetCellValue(obj.byqCapcity.ToString(), row, 4);
ex.SetCellValue(obj.byqMadeDate.Year.ToString(), row, 14);
ex.SetCellValue(obj.byqMadeDate.Month.ToString(), row, 16);
ex.SetCellValue(obj.byqMadeDate.Day.ToString(), row, 18);
ex.SetCellValue(obj.byqCurrentOne.ToString(), row, 23);
row += 2;//8
ex.SetCellValue(obj.byqLineGroup, row, 4);
ex.SetCellValue(obj.byqCycle, row, 14);
ex.SetCellValue(obj.byqCurrentTwo.ToString(), row, 23);
//活动页
//标题
ex.SetCellValue("配电变压器卡片(" + temptq.tqName + ")", 2, 1);
for (int k = 0; k < pagecount; k++)
{
ex.ActiveSheet(k + 1);
//标题
ex.SetCellValue("配电变压器卡片(" + temptq.tqName + ")", 2, 1);
//变压器变动内容
row = 12;
for (int i = 0; i < 3; i++)
{
if (k * 3 + i >= pjbdlist.Count)
{
break;
}
PJ_11byqbd tempobj = pjbdlist[k * 3 + i];
ex.SetCellValue(tempobj.azrq.Year.ToString(), row + i, 1);
ex.SetCellValue(tempobj.azrq.Month.ToString(), row + i, 3);
ex.SetCellValue(tempobj.azrq.Day.ToString(), row + i, 5);
ex.SetCellValue(tempobj.azdd, row + i, 7);
if (tempobj.ccrq.Year != 1900) {
ex.SetCellValue(tempobj.ccrq.Year.ToString(), row + i, 14);
ex.SetCellValue(tempobj.ccrq.Month.ToString(), row + i, 16);
ex.SetCellValue(tempobj.ccrq.Day.ToString(), row + i, 18);
ex.SetCellValue(tempobj.ccyy, row + i, 20);
}
}
//电压电流内容
row = 6;
for (int j = 0; j < 22; j++)
{
if (k * 22 + j >= pjdydllist.Count)
{
break;
}
PJ_11byqdydl tempobj = pjdydllist[k * 22 + j];
ex.SetCellValue(tempobj.clrq.Year.ToString(), row + j, 26);
ex.SetCellValue(tempobj.clrq.Month.ToString(), row + j, 28);
ex.SetCellValue(tempobj.clrq.Day.ToString(), row + j, 30);
ex.SetCellValue(tempobj.fjtwz, row + j, 32);
ex.SetCellValue(tempobj.ao.ToString(), row + j, 33);
ex.SetCellValue(tempobj.bo.ToString(), row + j, 34);
ex.SetCellValue(tempobj.co.ToString(), row + j, 35);
ex.SetCellValue(tempobj.a.ToString(), row + j, 36);
ex.SetCellValue(tempobj.b.ToString(), row + j, 37);
ex.SetCellValue(tempobj.c.ToString(), row + j, 38);
ex.SetCellValue(tempobj.ao2.ToString(), row + j, 39);
ex.SetCellValue(tempobj.bo2.ToString(), row + j, 40);
ex.SetCellValue(tempobj.co2.ToString(), row + j, 41);
}
//绝缘电阻测量内容
row = 16;
for (int l = 0; l < 9; l++)
{
if (k*9+l>=pjdzcllist.Count)
{
break;
}
if (l % 3 == 0 && l > 0)
{
row = 16 - l;
}
PJ_11byqdzcl tempobj = pjdzcllist[k * 9 + l];
if (l < 3)
{
ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 7);
ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 10);
ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 12);
ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 7);
ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 7);
ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 7);
row++;
}
else if (3 <= l && l < 6)
{
ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 14);
ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 16);
ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 18);
ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 14);
ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 14);
ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 14);
row++;
}
else if (6 <= l && l < 9)
{
ex.SetCellValue(tempobj.clrq.Year.ToString(), row + l, 20);
ex.SetCellValue(tempobj.clrq.Month.ToString(), row + l, 22);
ex.SetCellValue(tempobj.clrq.Day.ToString(), row + l, 24);
ex.SetCellValue(tempobj.one2one.ToString(), row + l + 1, 20);
ex.SetCellValue(tempobj.one2d.ToString(), row + l + 2, 20);
ex.SetCellValue(tempobj.two2d.ToString(), row + l + 3, 20);
row++;
}
}
}
//设第一工作表为当前工作表
ex.ActiveSheet(1);
ex.ShowExcel();
}
public void FillMutilRowsT(ExcelAccess ea, LP_Temple lp, string str, int cellcount, string arrCellPos, string coltemp) {
StringHelper help = new StringHelper();
//str = help.GetPlitString(str, cellcount);
string[] arrRst = str.Split(new string[] { "\r\n" }, StringSplitOptions.None);
if (lp.ExtraWord == "横向") {
for (int i = 0; i < arrRst.Length; i++) {
ea.SetCellValue("'" + arrRst[i], GetCellPos(arrCellPos)[0], GetCellPos(arrCellPos)[1] + i);
if (valuehs.ContainsKey(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1]] as WF_TableFieldValue;
tfv.ControlValue = arrRst[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "-" + coltemp;
tfv.XExcelPos = GetCellPos(arrCellPos)[0];
tfv.YExcelPos = GetCellPos(arrCellPos)[1] + i;
tfv.ExcelSheetName = activeSheetName;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = arrRst[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "-" + coltemp;
tfv.XExcelPos = GetCellPos(arrCellPos)[0];
tfv.YExcelPos = GetCellPos(arrCellPos)[1] + i;
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1], tfv);
}
}
} else {
for (int i = 0; i < arrRst.Length; i++) {
ea.SetCellValue("'" + arrRst[i], GetCellPos(arrCellPos)[0] + i, GetCellPos(arrCellPos)[1]);
if (valuehs.ContainsKey(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1])) {
WF_TableFieldValue tfv = valuehs[lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1]] as WF_TableFieldValue;
tfv.ControlValue = arrRst[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "-" + coltemp;
tfv.XExcelPos = GetCellPos(arrCellPos)[0] + i;
tfv.YExcelPos = GetCellPos(arrCellPos)[1];
tfv.ExcelSheetName = activeSheetName;
} else {
WF_TableFieldValue tfv = new WF_TableFieldValue();
tfv.ControlValue = arrRst[i];
tfv.FieldId = lp.LPID;
tfv.FieldName = lp.CellName + "-" + coltemp;
tfv.XExcelPos = GetCellPos(arrCellPos)[0] + i;
tfv.YExcelPos = GetCellPos(arrCellPos)[1];
tfv.ExcelSheetName = activeSheetName;
valuehs.Add(lp.LPID + "$" + Convert.ToString(GetCellPos(arrCellPos)[0] + i) + "|" + GetCellPos(arrCellPos)[1], tfv);
}
}
}
}
