后台自动生成Excel，指定生成路径

来源：互联网发布：java se环境包括jdk吗编辑：程序博客网时间：2024/04/29 17:57

方法一：

//自动生成Excel
protected string AutoGenerateExcel()
{
try
{
string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
string sdate = DateTime.Now.ToString("yyyyMMddHHmmssfff");
string path = @baseDirectory + @"BondQueryFiles\" + sdate + "保证金情况.xls";
Excel.Application excel;
excel = new Excel.Application();
// excel.Visible = false;
object ms = Type.Missing;
Excel.Workbook wk = excel.Workbooks.Add(ms);

Excel.Worksheet ws = (Excel.Worksheet)wk.Sheets[1];
ws.Name = DateTime.Now.ToString("yyyyMMdd") + "保证金到期情况汇总";
String strTitle = "保证金明细";

const int columnCount = 12;

int z = 1;
//设置标题
Excel.Range titleRange = ws.Range[ws.Cells[z, 1], ws.Cells[z, columnCount]];
titleRange.Merge(true);//合并单元格
titleRange.Value2 = strTitle; //设置单元格内文本
titleRange.Font.Name = "宋体";//设置字体
titleRange.Font.Size = 18;//字体大小
titleRange.Font.Bold = true;//加粗显示
titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中
titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
titleRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细

string[] strHead = new string[columnCount] { "一级部门", "二级部门", "收款单位", "项目名称", "保证金金额", "借/付款时间", "是否收回", "应收回时间", "经办人", "借/付款方式", "收据情况", "描述" };
int[] columnWidth = new int[columnCount] { 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 };

z++;
for (int i = 0; i < columnCount; i++)
{
Excel.Range headRange = ws.Cells[z, i + 1] as Excel.Range;//获取表头单元格
headRange.Value2 = strHead[i];//设置单元格文本
headRange.Font.Name = "宋体";//设置字体
headRange.Font.Size = 12;//字体大小
headRange.Font.Bold = true;//加粗显示
headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中
headRange.ColumnWidth = columnWidth[i];//设置列宽
headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
headRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;//边框常规粗细
}

z++;
string strWhere = "IsTakeback = 0 and TakebackTime >= '" + DateTime.Now.Date.AddDays(-7).ToString("yyyy-MM-dd") + "' and TakebackTime <= '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and AgentId=" + p_id + "";
DataTable dt = b_performanceBond.GetList_InformationQuery("0", strWhere, 0, 0).Tables[0];

if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ws.Cells[z, 1] = b_PrimaryDept.GetModel(Convert.ToInt32(dr["PrimaryDeptId"])).Caption;
m_SecondaryDept=b_SecondaryDept.GetModel(Convert.ToInt32(dr["SecondaryDeptId"]));
if (m_SecondaryDept != null)
ws.Cells[z, 2] = m_SecondaryDept.Caption;
ws.Cells[z, 3] = dr["ReceiveUnit"];
ws.Cells[z, 4] = dr["Project"];
ws.Cells[z, 5] = dr["BondAmount"];
ws.Cells[z, 6] = dr["BorrowTime"];
if (dr["IsTakeback"].Equals(true))
ws.Cells[z, 7] = "是";
else
ws.Cells[z, 7] = "否";
ws.Cells[z, 8] = dr["TakebackTime"];
ws.Cells[z, 9] = b_employee.GetModel(Convert.ToInt32(dr["AgentId"])).Name;
ws.Cells[z, 10] = b_borrowMethod.GetModel(Convert.ToInt32(dr["BorrowMethodId"])).Caption;
ws.Cells[z, 11] = dr["Receipt"];
ws.Cells[z, 12] = dr["Description"];
z++;
}
}

//C#操作Excel保存方式一：保存WorkBook
wk.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws = null;
wk.Close(true, Type.Missing, Type.Missing);
wk = null;
excel.Quit();
//这一句是非常重要的，否则Excel对象不能从内存中退出
GC.Collect();

return path;
}
catch()
{
}
}

0 0