//在服务器中找到报表模板，从数据库中选择数据，将数据写入模板中，把该文件保存在服务器的C://temp目录下；客户端再下载该文件，就能在客户端进行浏览
public string CreateExcel(string filename, string brand, string startDate, string endDate, string team, string style, string month, DateTime date, bool merge)
{
MSYS.Common.ExcelExport openXMLExcel = null;
try
{
MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator();
string bookid = opt.GetSegValue("select F_ID from ht_sys_excel_book where F_NAME = '" + filename + "'", "F_ID");
string booktype = opt.GetSegValue("select F_TYPE from ht_sys_excel_book where F_NAME = '" + filename + "'", "F_TYPE");
string basedir = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
string strFolderPath = basedir + @"\TEMP";
if (!System.IO.Directory.Exists(strFolderPath))
{
// 目录不存在，建立目录
System.IO.Directory.CreateDirectory(strFolderPath);
}
DirectoryInfo dyInfo = new DirectoryInfo(strFolderPath);
//获取文件夹下所有的文件
foreach (FileInfo feInfo in dyInfo.GetFiles())
{
//判断文件日期是否小于今天，是则删除
if (feInfo.CreationTime < DateTime.Now.AddMinutes(-2))
{
feInfo.Delete();
}
}
foreach (DirectoryInfo dir in dyInfo.GetDirectories())
{
if (dir.CreationTime < DateTime.Now.AddMinutes(-2))
{
dir.Delete(true);
}
}
//导出文件模板所在位置
String sourcePath = basedir + @"templates\" + booktype + @"\" + filename + ".xls";
String filepath = basedir + @"TEMP\" + filename + date.ToString("HHmmss") + style;
//bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之
//System.IO.File.Copy(sourcePath, filepath, isrewrite);
string query = "select * from ht_sys_excel_seg where F_BOOK_ID = '" + bookid + "' order by F_DES";
DataSet data = opt.CreateDataSetOra(query);
//申明一个ExcelSaveAs对象，该对象完成将数据写入Excel的操作
openXMLExcel = new MSYS.Common.ExcelExport(sourcePath, false);
if (data.Tables[0].Select().GetLength(0) > 0)
{
DataRow[] rows = data.Tables[0].Select();
foreach (DataRow row in rows)
{
string sqlstr = row["F_SQL"].ToString();
//设定选择的数据的日期及牌号等信息
if (brand != "")
{
sqlstr = sqlstr.Replace("$brand$", brand);
}
if (startDate != "")
{
sqlstr = sqlstr.Replace("$startDate$", startDate);
}
if (endDate != "")
{
sqlstr = sqlstr.Replace("$endDate$", endDate);
}
if (team != "")
{
sqlstr = sqlstr.Replace("$team$", team);
}
if (month != "")
{
sqlstr = sqlstr.Replace("$month$", month);
}
if (sqlstr != "")
{
//将选择的数据写入Excel
if (sqlstr.Length > 4 && sqlstr.Substring(0, 3) == "STR")
{
sqlstr = sqlstr.Substring(4);
Response.Write(openXMLExcel.SetCurrentSheet(Convert.ToInt32(row["F_SHEETINDEX"].ToString())));
Response.Write(openXMLExcel.WriteData(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, sqlstr));
}
if (sqlstr.Length > 5 && sqlstr.Substring(0, 4) == "Proc")
{
int pos = sqlstr.IndexOf('@');
int pos2 = sqlstr.IndexOf('#');
string proc = sqlstr.Substring(5, pos - 5);
int paracount = Regex.Matches(sqlstr, @"@").Count;
List <string> seglist = new List <string>();
List <string> paralist = new List <string>();
for (int i = 0; i < paracount; i++)
{
seglist.Add(sqlstr.Substring(pos + 1, pos2 - pos - 1));
pos = sqlstr.IndexOf('@', pos2);
if (pos > 0)
{
paralist.Add(sqlstr.Substring(pos2 + 1, pos - pos2 - 1));
pos2 = sqlstr.IndexOf('#', pos);
}
else
{
paralist.Add(sqlstr.Substring(pos2 + 1));
}
}
opt.ExecProcedures(proc, seglist.ToArray(), paralist.ToArray());
}
if (sqlstr.Length > 20 && sqlstr.Substring(0, 3) == "SQL")
{
sqlstr = sqlstr.Substring(4).Trim();
System.Diagnostics.Debug.WriteLine(sqlstr);
bool hasCaption = false;
if (sqlstr.Substring(0, 1) == "$")
{
hasCaption = true;
sqlstr = sqlstr.Substring(1);
}
DataSet set = new DataSet();
if (sqlstr.Substring(0, 5) == "SHIFT")
{
sqlstr = sqlstr.Substring(6).Trim();
set = opt.ShiftTable(sqlstr);
}
else
{
set = opt.CreateDataSetOra(sqlstr);
}
if (set != null)
{
DataTable dt = set.Tables[0];
openXMLExcel.SetCurrentSheet(Convert.ToInt32(row["F_SHEETINDEX"].ToString()));
if (merge)
{
if (hasCaption)
{
openXMLExcel.WriteDataRerangeWithCaption(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt);
}
else
{
openXMLExcel.WriteDataRerange(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt);
}
}
else
{
if (hasCaption)
{
openXMLExcel.WriteDataIntoWorksheetWithCaption(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt);
}
else
{
openXMLExcel.WriteDataIntoWorksheet(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt);
}
}
}
}
}
}
}
///客户端再下载该文件，在客户端进行浏览
FileInfo fi = new FileInfo(filepath);
if (fi.Exists) //判断文件是否已经存在,如果存在就删除!
{
fi.Delete();
}
if (style == ".xlsx" || style == ".xls")
{
openXMLExcel.SaveAs(filepath);
}
else
{
openXMLExcel.SaveAsHtm(filepath);
System.Diagnostics.Debug.WriteLine("路径");
System.Diagnostics.Debug.WriteLine(filepath);
}
openXMLExcel.Dispose();
openXMLExcel = null;
KillProcess("EXCEL.EXE");
return("Success");
}
catch (Exception e)
{
if (openXMLExcel != null)
{
openXMLExcel.Dispose();
}
return(e.Message);
}
finally
{
}
}
