从数据库读取数据写入excel并调用Winrar加密压缩输出
参考了园子里一些朋友的文章，在下面的处理方式中仍然存在一些问题，更改excel Application的运行帐户为network Service后可以正常下载，但是excel进程无法正常关闭，也试验了很多种方法，仍然没有最后解决。
代码如下：
string save_path = "", tick = "";
try
{
ExcelOperate excelOperate = new ExcelOperate();
string temp_path = Server.MapPath("xls_files");//生成的文件存放路径
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
}
////获取批次信息
using (IDataReader dr = ManageBAO.GetCardBatch(Request.QueryString["BID"]))
{
if (dr.Read())
{
BatchName = dr["BatchName"].ToString();
Remark = dr["Remark"].ToString();
CreateTime = dr["CreateTime"].ToString();
AppUser = ManageBAO.GetUserNameByID(dr["UserID"].ToString());
CreateUser = ManageBAO.GetUserNameByID(dr["CreateUser"].ToString());
}
}
//获取该批次的充值卡列表
System.Data.DataTable dt = ManageBAO.GetBatchCard(Request.QueryString["BID"]);
//建立一个Excel.Application的新进程
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
return;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return;
}
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 5]).Merge(Missing.Value); //横向合并
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = BatchName; //标题
excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体
excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色
excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小
excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边
worksheet.Cells[2, 2] = "申请人：" + AppUser;
worksheet.Cells[2, 3] = "生成时间：" + CreateTime;
worksheet.Cells[2, 4] = "审核人：" + CreateUser;
worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 5]).Merge(Missing.Value);
worksheet.Cells[3, 1] = "备注：" + Remark;
worksheet.Cells[4, 1] = "序号";
worksheet.Cells[4, 2] = "卡号";
worksheet.Cells[4, 3] = "密码";
worksheet.Cells[4, 4] = "点数";
worksheet.Cells[4, 5] = "过期时间";
excelOperate.SetBold(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5]); //黑体
worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[4, 5]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
excelOperate.SetHAlignRight(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5]);
excelOperate.SetBgColor(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5], System.Drawing.Color.Silver);//背景色
//定义一个数组，将数据保存在这个数组中
object[,] rawData = new object[dt.Rows.Count, dt.Columns.Count + 1];
//将数组的第一列计算为序号
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row, 0] = row + 1;
}
//将数据写入数组
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row, col + 1] = dt.Rows[row].ItemArray[col].ToString(); //如不ToString()则时间类型不能被默认读出
}
}
//将数据写入sheet
worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[dt.Rows.Count + 5, 5]).Value2 = rawData;
//这种方式验证浪费资源
//int rowNum = 0;
//for (int i = 0; i < dv.Count; i++)
//{
// rowNum = i + 1;
// worksheet.Cells[5 + i, 1] = rowNum;
// worksheet.Cells[5 + i, 2] = dv[i].Row[0].ToString();
// worksheet.Cells[5 + i, 3] = dv[i].Row[1].ToString();
// worksheet.Cells[5 + i, 4] = dv[i].Row[2].ToString();
// worksheet.Cells[5 + i, 5] = dv[i].Row[3].ToString();
// excelOperate.SetBold(worksheet, worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 1]); //黑体
// excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 5]);//居中
// worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 5]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观
//}
//定义各列宽度
excelOperate.SetColumnWidth(worksheet, "A", 10);
excelOperate.SetColumnWidth(worksheet, "B", 20);
excelOperate.SetColumnWidth(worksheet, "C", 20);
excelOperate.SetColumnWidth(worksheet, "D", 10);
excelOperate.SetColumnWidth(worksheet, "E", 20);
worksheet.Name = BatchName;
tick = DateTime.Now.Ticks.ToString();
save_path = temp_path + "\\" + tick + ".xlsx";
workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excelOperate.Dispose(worksheet, workbook, app);//最后关闭Excel进程
//**************直接输出的方式，只能输入xls，不能为xlsx，如数据量超过excel2003的最大行数？？？******************
//StringWriter stringWriter = new StringWriter();
//HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
//GridView excel = new GridView();
//excel.DataSource = dt.DefaultView;
//excel.DataBind();
//excel.RenderControl(htmlWriter);
////这里指定文件的路径
//System.IO.StreamWriter sw = new StreamWriter(save_path);
//sw.Write(stringWriter.ToString());
//sw.Close();
//开始压缩
String Rar;
RegistryKey Reg;
Object Obj;
String Info;
ProcessStartInfo StartInfo;
Process Process;
string pass = Functions.GetRandomPassword(6);
Reg = Registry.ClassesRoot.OpenSubKey(@"Applications\WinRAR.exe\Shell\Open\Command");
Obj = Reg.GetValue("");
Rar = Obj.ToString();
Reg.Close();
Rar = Rar.Substring(1, Rar.Length - 7);
Info = "a -p" + pass + " -r -ep1 " + tick + ".rar " + @save_path + " 1.rar";//这里为rar的压缩命令格式
//Info = "a -r -ep1 1.rar "+aa+" 1.rar";//这里为rar的压缩命令格式
StartInfo = new ProcessStartInfo();
StartInfo.FileName = Rar;
StartInfo.Arguments = Info;
StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
StartInfo.WorkingDirectory = temp_path;//获取或设置要启动的进程的初始目录。
Process = new Process();
Process.StartInfo = StartInfo;
Process.Start();
Process.WaitForExit();
if (Process.HasExited)
{
//int iExitCode = Process.ExitCode;
//if (iExitCode == 0)
//{
// Response.Write(iExitCode.ToString() + " 正常完成");
//}
//else
//{
// Response.Write(iExitCode.ToString() + " 有错完成");
//}
File.Delete(save_path);
}
//将密码以需要的方式保存
Page.Response.Clear();
bool success = Functions.ResponseFile(tick + ".rar", temp_path + "\\" + tick + ".rar", 1000000);
if (success == true)
{
Response.Write("OK");
}
//Page.Response.End();
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger("Card");
log.Debug(ex.Message);
Response.Write(ex.Message);
}
代码如下：
string save_path = "", tick = ""; try {
ExcelOperate excelOperate = new ExcelOperate(); string temp_path = Server.MapPath("xls_files");//生成的文件存放路径 if (!Directory.Exists(temp_path)) {
Directory.CreateDirectory(temp_path); } ////获取批次信息 using (IDataReader dr = ManageBAO.GetCardBatch(Request.QueryString["BID"])) { if (dr.Read()) { BatchName = dr["BatchName"].ToString(); Remark = dr["Remark"].ToString(); CreateTime = dr["CreateTime"].ToString(); AppUser = ManageBAO.GetUserNameByID(dr["UserID"].ToString()); CreateUser = ManageBAO.GetUserNameByID(dr["CreateUser"].ToString()); } } //获取该批次的充值卡列表 System.Data.DataTable dt = ManageBAO.GetBatchCard(Request.QueryString["BID"]); //建立一个Excel.Application的新进程 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { return; } app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); if (worksheet == null) { return; } worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 5]).Merge(Missing.Value); //横向合并 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = BatchName; //标题 excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中 excelOperate.SetBgColor(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], System.Drawing.Color.Red);//背景色 excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 16);//字体大小 excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 32.25);//行高 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边 worksheet.Cells[2, 2] = "申请人：" + AppUser; worksheet.Cells[2, 3] = "生成时间：" + CreateTime; worksheet.Cells[2, 4] = "审核人：" + CreateUser; worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 5]).Merge(Missing.Value); worksheet.Cells[3, 1] = "备注：" + Remark; worksheet.Cells[4, 1] = "序号"; worksheet.Cells[4, 2] = "卡号"; worksheet.Cells[4, 3] = "密码"; worksheet.Cells[4, 4] = "点数"; worksheet.Cells[4, 5] = "过期时间"; excelOperate.SetBold(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5]); //黑体 worksheet.get_Range(worksheet.Cells[4, 1], worksheet.Cells[4, 5]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); excelOperate.SetHAlignRight(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5]); excelOperate.SetBgColor(worksheet, worksheet.Cells[4, 1], worksheet.Cells[4, 5], System.Drawing.Color.Silver);//背景色 //定义一个数组，将数据保存在这个数组中 object[,] rawData = new object[dt.Rows.Count, dt.Columns.Count + 1]; //将数组的第一列计算为序号 for (int row = 0; row < dt.Rows.Count; row++) { rawData[row, 0] = row + 1; } //将数据写入数组 for (int col = 0; col < dt.Columns.Count; col++) { for (int row = 0; row < dt.Rows.Count; row++) { rawData[row, col + 1] = dt.Rows[row].ItemArray[col].ToString(); //如不ToString()则时间类型不能被默认读出 } } //将数据写入sheet worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[dt.Rows.Count + 5, 5]).Value2 = rawData; //这种方式验证浪费资源 //int rowNum = 0; //for (int i = 0; i < dv.Count; i++) //{ // rowNum = i + 1; // worksheet.Cells[5 + i, 1] = rowNum; // worksheet.Cells[5 + i, 2] = dv[i].Row[0].ToString(); // worksheet.Cells[5 + i, 3] = dv[i].Row[1].ToString(); // worksheet.Cells[5 + i, 4] = dv[i].Row[2].ToString(); // worksheet.Cells[5 + i, 5] = dv[i].Row[3].ToString(); // excelOperate.SetBold(worksheet, worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 1]); //黑体 // excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 5]);//居中 // worksheet.get_Range(worksheet.Cells[5 + i, 1], worksheet.Cells[5 + i, 5]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观 //} //定义各列宽度 excelOperate.SetColumnWidth(worksheet, "A", 10); excelOperate.SetColumnWidth(worksheet, "B", 20); excelOperate.SetColumnWidth(worksheet, "C", 20); excelOperate.SetColumnWidth(worksheet, "D", 10); excelOperate.SetColumnWidth(worksheet, "E", 20); worksheet.Name = BatchName; tick = DateTime.Now.Ticks.ToString(); save_path = temp_path + "\\" + tick + ".xlsx"; workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excelOperate.Dispose(worksheet, workbook, app);//最后关闭Excel进程 //**************直接输出的方式，只能输入xls，不能为xlsx，如数据量超过excel2003的最大行数？？？****************** //StringWriter stringWriter = new StringWriter(); //HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter); //GridView excel = new GridView(); //excel.DataSource = dt.DefaultView; //excel.DataBind(); //excel.RenderControl(htmlWriter); ////这里指定文件的路径 //System.IO.StreamWriter sw = new StreamWriter(save_path); //sw.Write(stringWriter.ToString()); //sw.Close(); //开始压缩 String Rar; RegistryKey Reg; Object Obj; String Info; ProcessStartInfo StartInfo; Process Process; string pass = Functions.GetRandomPassword(6); Reg = Registry.ClassesRoot.OpenSubKey(@"Applications\WinRAR.exe\Shell\Open\Command"); Obj = Reg.GetValue(""); Rar = Obj.ToString(); Reg.Close(); Rar = Rar.Substring(1, Rar.Length - 7); Info = "a -p" + pass + " -r -ep1 " + tick + ".rar " + @save_path + " 1.rar";//这里为rar的压缩命令格式 //Info = "a -r -ep1 1.rar "+aa+" 1.rar";//这里为rar的压缩命令格式 StartInfo = new ProcessStartInfo(); StartInfo.FileName = Rar; StartInfo.Arguments = Info; StartInfo.WindowStyle = ProcessWindowStyle.Hidden; StartInfo.WorkingDirectory = temp_path;//获取或设置要启动的进程的初始目录。 Process = new Process(); Process.StartInfo = StartInfo; Process.Start(); Process.WaitForExit(); if (Process.HasExited) { //int iExitCode = Process.ExitCode; //if (iExitCode == 0) //{ // Response.Write(iExitCode.ToString() + " 正常完成"); //} //else //{ // Response.Write(iExitCode.ToString() + " 有错完成"); //} File.Delete(save_path); } //将密码以需要的方式保存 Page.Response.Clear(); bool success = Functions.ResponseFile(tick + ".rar", temp_path + "\\" + tick + ".rar", 1000000); if (success == true) { Response.Write("OK"); } //Page.Response.End(); } catch (Exception ex) { ILog log = LogManager.GetLogger("Card"); log.Debug(ex.Message); Response.Write(ex.Message); }