個人最近做的最多的重複工作就是excel导出
//导出事件，这个是有合并动态列的
double num1 = 0, num2 = 0, num3 = 0;
protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
num1 = 0; num2 = 0; num3 = 0;
DirectoryInfo fdir = new DirectoryInfo(Server.MapPath(@"\TmpReport"));
FileInfo[] files = fdir.GetFiles();
for (int i = 0; i < files.Length - 1; i++)
{
if (files[i].Name.IndexOf("tmp") >= 0)
{
try {
files[i].Delete();
}
catch (Exception ex)
{
}
}
}
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
FileInfo fi = new FileInfo(Server.MapPath(@"\Excel\每日信貸報告.xls"));
fi.CopyTo(Server.MapPath(@"\TmpReport\tmp" + time + ".xls"));
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook xlBook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = new Microsoft.Office.Interop.Excel.Worksheet();
xlBook = xlApp.Workbooks.Open(Server.MapPath(@"\TmpReport\tmp" + time + ".xls"));
xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
int tmpid = 2;
string rowSheet = "";
string 可簽額 = "";
string 戶主名 = "";
int count1 = 2;
int count2 = 2;
int Colorindex = 0;
for (int i = 0; i < dtList.Rows.Count; i++)
{
string rowSheet1 = Convert.ToString(dtList.Rows[i]["戶號"]);
if (rowSheet1 == rowSheet)
{
//xlSheet.Cells[tmpid, 1] = ""; //xlSheet.Cells[tmpid, 2] = ""; //xlSheet.Cells[tmpid, 3] = ""; }
else {
if (rowSheet != "")
{
Microsoft.Office.Interop.Excel.Range integral = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("A" + count1, "A" + (count2 - 1));
integral.MergeCells = true;
integral.Value = 可簽額;
integral.Font.Size = 16;
integral.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Microsoft.Office.Interop.Excel.Range integral1 = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("B" + count1, "B" + (count2 - 1));
integral1.MergeCells = true;
integral1.Value = rowSheet;
integral1.Font.Size = 16;
// integral1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; integral1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Microsoft.Office.Interop.Excel.Range integral2 = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("C" + count1, "C" + (count2 - 1));
integral2.MergeCells = true;
integral2.Value = 戶主名;
integral2.Font.Size = 16;
integral2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Colorindex = 15;
if (Colorindex != 0)
{
Microsoft.Office.Interop.Excel.Range integrals = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("a" + tmpid, "I" + tmpid);
integrals.Interior.ColorIndex = 15;
Colorindex = 0;
}
count2++;
tmpid++;
count1 = count2;
//xlSheet.Cells[tmpid, 1] = dtList.Rows[i]["可簽額"]; //xlSheet.Cells[tmpid, 2] = dtList.Rows[i]["戶號"]; //xlSheet.Cells[tmpid, 3] = dtList.Rows[i]["戶主名"]; }
rowSheet = rowSheet1;
可簽額 = Convert.ToString(dtList.Rows[i]["可簽額"]);
戶主名 = Convert.ToString(dtList.Rows[i]["戶主名"]);
}
num1 += Convert.ToDouble(dtList.Rows[i]["貸款額"].ToString());
num2 += Convert.ToDouble(dtList.Rows[i]["還款額"].ToString());
num3 += Convert.ToDouble(dtList.Rows[i]["餘額"].ToString());
xlSheet.Cells[tmpid, 4] = dtList.Rows[i]["借款人"];
xlSheet.Cells[tmpid, 5] = dtList.Rows[i]["單編號"];
double a = Convert.ToDouble(dtList.Rows[i]["貸款額"].ToString());
double b = Convert.ToDouble(dtList.Rows[i]["還款額"].ToString());
double c = Convert.ToDouble(dtList.Rows[i]["餘額"].ToString());
string aStr = "", bStr = "", cStr = "";
if (a != 0)
{
aStr = a.ToString("#,##0.####");
}
if (b != 0)
{
bStr = b.ToString("#,##0.####");
}
if (c != 0)
{
cStr = c.ToString("#,##0.####");
}
xlSheet.Cells[tmpid, 6] = aStr;
xlSheet.Cells[tmpid, 7] = bStr;
xlSheet.Cells[tmpid, 8] = cStr;
xlSheet.Cells[tmpid, 9] = dtList.Rows[i]["下單號"];
tmpid = tmpid + 1;
count2++;
}
Microsoft.Office.Interop.Excel.Range integral3 = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("A" + count1, "A" + (count2 - 1));
integral3.MergeCells = true;
integral3.Value = 可簽額;
integral3.Font.Size = 16;
integral3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral3.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Microsoft.Office.Interop.Excel.Range integral4 = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("B" + count1, "B" + (count2 - 1));
integral4.MergeCells = true;
integral4.Value = rowSheet;
integral4.Font.Size = 16;
integral4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral4.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
Microsoft.Office.Interop.Excel.Range integral5 = (Microsoft.Office.Interop.Excel.Range)xlSheet.get_Range("C" + count1, "C" + (count2 - 1));
integral5.MergeCells = true;
integral5.Value = 戶主名;
integral5.Font.Size = 16;
integral5.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
integral5.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//獲取行 Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 1];
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 1];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 2];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 3];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 4];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 5];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 6];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 7];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 8];
rg.Interior.ColorIndex = 15;
rg = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[tmpid, 9];
rg.Interior.ColorIndex = 15;
xlSheet.Cells[tmpid, 1] = "";
xlSheet.Cells[tmpid, 2] = "";
xlSheet.Cells[tmpid, 3] = "";
xlSheet.Cells[tmpid, 4] = "";
xlSheet.Cells[tmpid, 5] = "合計：";
xlSheet.Cells[tmpid, 6] = num1.ToString("#,##0.####");
xlSheet.Cells[tmpid, 7] = num2.ToString("#,##0.####");
xlSheet.Cells[tmpid, 8] = num3.ToString("#,##0.####");
xlSheet.SaveAs(Server.MapPath(@"\TmpReport\tmp" + time + "1.xls"));
xlBook.Close();
xlApp.Quit();
Response.Expires = 0;
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("每日信貸報告.xls"));
Response.WriteFile(Server.MapPath(@"\TmpReport\tmp" + time + "1.xls"));
Response.Flush();
Response.Clear();
}