本文介绍了如何使用VS2005和SQL2000开发工具，根据用户提供的固定模板生成报表。通过C#代码示例展示了如何打开Excel模板，填充数据并进行相关操作，涉及数据读取、Excel工作簿和工作表的操作等步骤。
前一阵做了一个项目，我负责报表的生成。要求使用用户给定的固定模板，使用vs2005＋sql2000 开发工具进行项目开发。本文只举一个例子。 下面代码 针对操作的模板以及数据库文档 见附件或图片。
private void pb_print(string ID, DataTable certify) //普板专用函数 调用版本
{
string strFileName, strsql, tempcells;
string strsqlhead;
DataSet dscertify;
int i, j;
int totalpage = 1, nowpage = 1, lastpage = 1, lastrecords = 0;
strFileName ="D://普板专用.xlt";
Microsoft.Office.Interop.Excel.Application myExcel;
myExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook myworkbook;
Microsoft.Office.Interop.Excel.Worksheet xsheet;
object missing = System.Reflection.Missing.Value;
//*********打开模板**********************************************
myworkbook = myExcel.Workbooks.Open(strFileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); //(strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet myworksheet = (Microsoft.Office.Interop.Excel.Worksheet)myworkbook.Sheets.get_Item(1);
//固定打开代码
//myExcel.Workbooks.Open(str, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
//ThisApplication.Visible = true;
myExcel.Visible = true; //显示模板代码
xsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(1);
strsql = "select NO as 序号, Grade as 钢级, BatchNo as 批号,HeatNo as 熔炼号, Thickness as 厚度, Width as 宽度, Longth as 长度, QTY as 数量,TONS as 重量,ComponentC as C,ComponentSi as Si,ComponentMn as Mn,ComponentP as P,ComponentS as S,ComponentAls as Als,ComponentCr as Cr,ComponentNi as Ni," +
"ComponentCu as Cu,ComponentNb as Nb,Ceq,YS as '屈服强度(Mpa)',TS as '抗拉强度(Mpa)',EL as 伸长率,SampleThickness as 试样厚度,Temperature as 温度,Impact1 as 冲击功1,Impact2 as 冲击功2,Impact3 as 冲击功3,StrainAgeingTemperature as 时效温度," +
"StrainAgeing1 as 时效1,StrainAgeing2 as 时效2,StrainAgeing3 as 时效3,CodeBendda as '冷弯d/a',CodeBendResualt as 冷弯结果,Fracture as 断口,UT as 超声波探伤UT,PRA1 as 断面收缩率1, PRA2 as 断面收缩率2,PRA3 as 断面收缩率3 ,ImpactDirection as 冲击功方向 ,CJGQKLX as 冲击功缺口 ,SXQKLX as 时效缺口类型 ,SXQYFX as 时效取样方向,ComponentMo as Mo from CertificateDetail where CertificateID =" + ID;
myDataTable = new DataTable("cer");//
certify = variableDeclare.dataBaceInterface.runQuery(strsql);
dscertify = variableDeclare.dataBaceInterface.runQuery(strsql, "cer");//
//dataGridView1.DataSource = dscertify;//
//dataGridView1.DataMember = "cer";//
DataRow dr;
dr = certify.Rows[0];
strsqlhead = "select * from Certificate where CertificateID =" + ID;
DataTable cerhead = variableDeclare.dataBaceInterface.runQuery(strsqlhead);
DataRow drhead;
drhead = cerhead.Rows[0];
xsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(nowpage);
//***********************************表头变量区域*************************************************************************
totalpage = (certify.Rows.Count / 10) + 1;
lastpage = totalpage;
nowpage = 1;
lastrecords = certify.Rows.Count % 10;
if (lastrecords == 0)
{
lastrecords = 10;
totalpage = totalpage - 1;
lastpage = totalpage;
}
string Number = drhead["Number"].ToString(), Number_enUS = drhead["Number"].ToString(), Customer = drhead["Customer"].ToString(), Customer_enUS = drhead["Customer_enUS"].ToString(), Purchaser = drhead["Purchaser"].ToString(), Purchaser_enUS = drhead["Purchaser_enUS"].ToString(),
TechnicalCondition = drhead["TechnicalCondition"].ToString(), TechnicalCondition_enUS = drhead["TechnicalCondition_enUS"].ToString();
string StockPORNo = drhead["StockPORNo"].ToString(), StockPORNo_enUS = drhead["StockPORNo_enUS"].ToString(), SteelGride = drhead["SteelGride"].ToString(), SteelGride_enUS = drhead["SteelGride_enUS"].ToString(), ContractNo = drhead["ContractNo"].ToString(),
ContractNo_enUS = drhead["ContractNo_enUS"].ToString(), CertificateNo = drhead["CertificateNo"].ToString(), CertificateNo_enUS = drhead["CertificateNo_enUS"].ToString();
string Supply_Condition = drhead["Supply_Condition"].ToString(), Supply_Condition_enUS = drhead["Supply_Condition_enUS"].ToString(), UT = drhead["UT"].ToString(), UT_enUS = drhead["UT_enUS"].ToString(), VisualDimension = drhead["VisualDimension"].ToString(), VisualDimension_enUS = drhead["VisualDimension_enUS"].ToString(),
Notes = drhead["Notes"].ToString(), Remarks = drhead["Remarks"].ToString();
string Statment = drhead["Statment"].ToString();
string Tablator = drhead["Tablator"].ToString();
string ImpactDirection = dr["冲击功方向"].ToString(), CJGQKLX = dr["冲击功缺口"].ToString(), SXQKLX = dr["时效缺口类型"].ToString(), SXQYFX = dr["时效取样方向"].ToString();
string chongjigong = "", shixiao = "";
if ((ImpactDirection != "") && (CJGQKLX != ""))
{
chongjigong = ImpactDirection + CJGQKLX + "型缺口";
}
if ((SXQYFX != "") && (SXQKLX != ""))
{
shixiao = SXQYFX + SXQKLX + "型缺口";
}
if (StockPORNo=="-1")
{
StockPORNo = ""; StockPORNo_enUS = "";
}
//if (dr["超声波探伤UT"].ToString() == "//")
//{
UT = ""; UT_enUS = "";
//}
string Datezhcn;
string DateEn;
try
{ date = Convert.ToDateTime(drhead["Datezhcn"].ToString()); }
catch (Exception excep) { }
Datezhcn = dateconvertCn(date);
DateEn = dateconvertEn(date);
//********************************************************************************************************************
for (lastpage = 1; lastpage < totalpage; lastpage++)
{
((Microsoft.Office.Interop.Excel.Worksheet)myworkbook.Worksheets.get_Item(1)).Copy(System.Reflection.Missing.Value, myworkbook.Worksheets[lastpage]);
xsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(lastpage + 1);
xsheet.Name = "sheet" + (lastpage + 1).ToString();
}
//**********************************打印数据区域**********************************************
//****************************除最后一页外的整页打印*****************************************************************
while (nowpage != lastpage)
{
xsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(nowpage);
//*************************************************每页打印表头区域************************************************
//xsheet.Cells[3, "AL"] = StockPORNo; xsheet.Cells[5, "AL"] = "123445678";
xsheet.Cells[3, "AL"] = Number; xsheet.Cells[5, "AL"] = StockPORNo;
xsheet.Cells[4, "Al"] = Number_enUS; xsheet.Cells[6, "AL"] = StockPORNo_enUS;
xsheet.Cells[3, "C"] = Customer; xsheet.Cells[7, "C"] = SteelGride;
xsheet.Cells[4, "C"] = Customer_enUS; xsheet.Cells[8, "C"] = SteelGride_enUS;
xsheet.Cells[5, "C"] = Purchaser; xsheet.Cells[7, "AL"] = ContractNo;
xsheet.Cells[6, "C"] = Purchaser_enUS; xsheet.Cells[8, "AL"] = ContractNo_enUS;
xsheet.Cells[9, "C"] = TechnicalCondition; xsheet.Cells[9, "AL"] ="'"+ CertificateNo;
xsheet.Cells[10, "C"] = TechnicalCondition_enUS; xsheet.Cells[10, "AL"] ="'"+ CertificateNo_enUS;
//**********************
xsheet.Cells[26, "T"] = UT; xsheet.Cells[26, "AI"] = VisualDimension;
xsheet.Cells[27, "T"] = UT_enUS; xsheet.Cells[27, "AI"] = VisualDimension_enUS;
xsheet.Cells[28, "C"] = Notes; xsheet.Cells[31, "AA"] = Datezhcn; xsheet.Cells[32, "AA"] = DateEn;
xsheet.Cells[29, "C"] = Remarks; xsheet.Cells[31, "AM"] = "共 " + totalpage + " 页 " + "第 " + nowpage + " 页";
xsheet.Cells[30, "C"] = Statment; xsheet.Cells[32, "AM"] = "Page " + nowpage + " Of " + totalpage;
xsheet.Cells[26, "G"] = Supply_Condition; xsheet.Cells[31, "C"] = Tablator;
xsheet.Cells[27, "G"] = Supply_Condition_enUS;
xsheet.Cells[12, "Y"] = chongjigong;
xsheet.Cells[13, "AD"] = shixiao;
//*****************************************************************************************************************
for (i = 0; i < 10; i++)
{
dr = certify.Rows[i + (10 * (nowpage - 1))];
for (j = 0; j < 19; j++)
{
tempcells = dr[j].ToString();
if ((tempcells == "") || (tempcells == "-1.00")) //------------------------0809 23:23
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 1)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 1)] = tempcells;
}
}
// xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = "//";//-------------------------0831 23:03 ComponentMo as Mo
xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = dr["Mo"].ToString();
if (dr["Mo"].ToString() == "")
{
xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = "//"; //-------------------------1018 14:41 by leo
}
for (j = 19; j < 30; j++)
{
tempcells = dr[j].ToString();
if (tempcells == "")
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 2)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 2)] = tempcells;
}
}
for (j = 29; j < 39; j++)
{
tempcells = dr[j].ToString();
if (tempcells == "")
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 5)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 5)] = tempcells;
}
}
if (dr["超声波探伤UT"].ToString() != "//")
{
//drhead["UT"].ToString(), UT_enUS = drhead["UT_enUS"].ToString() //-----------------0816 15:53
xsheet.Cells[26, "T"] = drhead["UT"].ToString();// UT;
xsheet.Cells[27, "T"] = drhead["UT_enUS"].ToString();// UT_enUS;
}
}
nowpage++;
}
//************打印最后一页*************************************************************
xsheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Worksheets.get_Item(lastpage);
xsheet.Cells[3, "AL"] = Number; xsheet.Cells[5, "AL"] = StockPORNo;
xsheet.Cells[4, "Al"] = Number_enUS; xsheet.Cells[6, "AL"] = StockPORNo_enUS;
xsheet.Cells[3, "C"] = Customer; xsheet.Cells[7, "C"] = SteelGride;
xsheet.Cells[4, "C"] = Customer_enUS; xsheet.Cells[8, "C"] = SteelGride_enUS;
xsheet.Cells[5, "C"] = Purchaser; xsheet.Cells[7, "AL"] = ContractNo;
xsheet.Cells[6, "C"] = Purchaser_enUS; xsheet.Cells[8, "AL"] = ContractNo_enUS;
xsheet.Cells[9, "C"] = TechnicalCondition; xsheet.Cells[9, "AL"] = "'"+CertificateNo;
xsheet.Cells[10, "C"] = TechnicalCondition_enUS; xsheet.Cells[10, "AL"] = "'"+CertificateNo_enUS;
//**********************
xsheet.Cells[26, "T"] = UT; xsheet.Cells[26, "AI"] = VisualDimension;
xsheet.Cells[27, "T"] = UT_enUS; xsheet.Cells[27, "AI"] = VisualDimension_enUS;
xsheet.Cells[28, "C"] = Notes; xsheet.Cells[31, "AA"] = Datezhcn; xsheet.Cells[32, "AA"] = DateEn;
xsheet.Cells[29, "C"] = Remarks; xsheet.Cells[31, "AM"] = "共 " + totalpage + " 页 " + "第 " + nowpage + " 页";
xsheet.Cells[30, "C"] = Statment; xsheet.Cells[32, "AM"] = "Page " + nowpage + " Of " + totalpage;
xsheet.Cells[26, "G"] = Supply_Condition; xsheet.Cells[31, "C"] = Tablator;
xsheet.Cells[27, "G"] = Supply_Condition_enUS;
xsheet.Cells[12, "Y"] = chongjigong;
xsheet.Cells[13, "AD"] = shixiao;
for (i = 0; i < lastrecords; i++)
{
dr = certify.Rows[i + (10 * (nowpage - 1))];
for (j = 0; j < 19; j++)
{
tempcells = dr[j].ToString();
if ((tempcells == "") || (tempcells == "-1.00")) //------------------------0809 23:23
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 1)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 1)] = tempcells;
}
}
//xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = dr["Mo"].ToString();//-------------------------0831 23:03 ComponentMo as Mo
if (dr["Mo"].ToString() == "")
{
xsheet.Cells[i + 15, ExcelColNumberToColText(20)] = "//";
}
for (j = 19; j < 30; j++)
{
tempcells = dr[j].ToString();
if (tempcells == "")
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 2)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 2)] = tempcells;
}
}
for (j = 29; j < 39; j++)
{
tempcells = dr[j].ToString();
if (tempcells == "")
{
tempcells = "//";
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 5)] = tempcells;
}
else
{
xsheet.Cells[i + 15, ExcelColNumberToColText(j + 5)] = tempcells;
}
}
if (dr["超声波探伤UT"].ToString() != "//")
{
//drhead["UT"].ToString(), UT_enUS = drhead["UT_enUS"].ToString() //-----------------0816 15:53
xsheet.Cells[26, "T"] = drhead["UT"].ToString();// UT;
xsheet.Cells[27, "T"] = drhead["UT_enUS"].ToString();// UT_enUS;
}
}
if (i != 0)
{
xsheet.Cells[i + 15, ExcelColNumberToColText(2)] = "以下空白";
xsheet.Cells[i + 15, ExcelColNumberToColText(3)] = " Here below empty";
}
}
扫一扫
3699
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
