110,533
社区成员
发帖
与我相关
我的任务
分享
public void KJBExcel1()
{
try
{
//打开一个excel对象
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(System.Windows.Forms.Application.StartupPath + @"\Temp\xxx\yyy数据统计表.xls");
//获取一个工作表
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets["1-2"];
//查询数据库
DBHelper dbh = new DBHelper();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "KJBExcel1.xml");
XmlNodeList nodeList = xmlDoc.SelectSingleNode("titles").ChildNodes;
string sql = "select distinct t_month,t_year from timeinterval order by t_year";
DataSet ds2 = dbh.GetList(sql);
if (ds2.Tables[0].Rows.Count != 0)
{
foreach (DataRow dr in ds2.Tables[0].Rows)
{
Range rang = ws.get_Range("A1", "A1");
string date = dr["t_year"].ToString() + "年" + dr["t_month"].ToString() + "月";
title = date + "份xxx数据统计表";
rang.set_Value(Type.Missing, title);//文件名
rang = ws.get_Range("G3", "G3");
string time = "统计时段:" + date + "1日-" + DateTime.Parse(date).AddMonths(1).AddDays(-1).ToString("yyyy年M月dd日");
rang.set_Value(Type.Missing, time);
rang = ws.get_Range("H31", "H31");
string time2 = "填报时间:" + DateTime.Parse(date).AddMonths(1).ToString("yyyy年M月") + "1日";
rang.set_Value(Type.Missing, time2);
foreach (XmlNode xn in nodeList)
{
XmlElement xe = (XmlElement)xn;
string sql2 = xe["SQL"].InnerText;
sql2 = sql2.Replace("$t_month$", dr["t_month"].ToString().Trim());
sql2 = sql2.Replace("$t_year$", dr["t_year"].ToString().Trim());
DataSet ds = dbh.GetList(sql2);
Dictionary<string, string> dic = new Dictionary<string, string>();
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string key = ds.Tables[0].Rows[i][2].ToString();
string value = ds.Tables[0].Rows[i][1].ToString();
dic.Add(key, value);
}
}
foreach (string key in dic.Keys)
{
foreach (XmlNode cn in xe.ChildNodes)
{
XmlElement xet = (XmlElement)cn;
if (xet.Name != "SQL")
{
if (dic.ContainsKey(xet.InnerText))
{
rang = ws.get_Range(xet.Name, xet.Name);
rang.set_Value(Type.Missing, dic[xet.InnerText]);
}
}
}
}
}
string filename = @"D:\xxx\{0}.xls";
filename = string.Format(filename, title);
if (File.Exists(filename) == false)
{
wb.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
}
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
//MessageBox.Show("生成成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}