使用EXCEL打印，在200条记录时大概需要2-5分钟，有的时候甚至跟慢，代码如下
/// <summary>
/// 根据摸版生成Excel文件
/// </summary>
/// <param name="ModeStream">保存Excel摸版得流文件</param>
/// <param name="FilePath">保存Excel文件得路径</param>
/// <param name="FileName">保存Excel文件得文件名</param>
/// <param name="DS">传递过来得DataSet里面包含1对多个多得表关系</param>
public WFPExcel(byte[] ModeStream, string FilePath, string FileName, DataSet DS, string wfpuser, ToolStripLabel TSWorkAppText)
{
bool HPageBreaks = false; //XU20090524
int puttype = 1; //是否插入行还是替换单元格
string fullPath = Path.Combine(FilePath, FileName);
//单页和多页区分开
//Dataset得表微对多个多的关系,只有一个主表，可以对应包含多个子表，根据主表得关键字来过滤子表来生成多个Excel
//Excel摸版里面"#WT_ABCDEF#.#姓名#"表示主表得一个指标"#WT_ABCDEF#"表示填充子表
//#WT_ABCDEF#.#姓名#的指标名为[图片][图片][PIC][IMG]都直接显示图片出来在相应的单元格子里面(子表目前不支持图片："#WT_ABCDEF#"的指标名)
//保存到本地
//临时文件路径生成的excel临时保存在这里
if (DS == null || DS.Tables == null || DS.Tables.Count == 0)
{ //没有数据直接输出模版
System.IO.FileStream modfs = new System.IO.FileStream(fullPath, System.IO.FileMode.OpenOrCreate);
modfs.Write(ModeStream, 0, ModeStream.Length);
modfs.Close();
modfs.Dispose();
return;
}
try
{
Random Randstr = new Random();
string s = String.Format("f{0:yyMMddHHmmss}{1:000}temp.xls", DateTime.Now, Randstr.Next(1000));
string FullPath = Path.Combine(FilePath,s);// String.Format("{0}{1}temp.xls", FilePath.Replace("//", "\\"), s);// +FileName;// @"\WFPExcel.xls"; // +FileName;
System.IO.FileStream fs = new System.IO.FileStream(FullPath, System.IO.FileMode.OpenOrCreate);
fs.Write(ModeStream, 0, ModeStream.Length);
fs.Close();
fs.Dispose();
if (!System.IO.File.Exists(FullPath))
{
throw new Exception(string.Format("文件不存在{0}", FullPath));
}
//打开excel文件
string tbname = "";//主表名称
string tbqname = ""; //对多sheet的处理的时候使用tbqname表示分别的tbname
string rngvalue = "";//单元格的值
string tbfied = "";
int tbrows = 1;//sheets数量
int izb = 0;
DataSet myworkFieldsets = new DataSet("workfieldset");
Excel.Application app = new Excel.ApplicationClass(); //启动excel的应用程序
try
{
#region
app.DisplayAlerts = false;//--不提示错误了
if (app == null)
{
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
throw new Exception("Excel无法启动");
}
//app.Visible = true;
Excel.Workbooks wbs = app.Workbooks; //启动excel的一个工作本集合（一个工作本表示一个excel文件）
//Excel.Workbook wb = wbs.Open(FullPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
try
{
Excel.Workbook wb = wbs.Add(FullPath);//打开一个excel文件
//return;
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; //获得excel的第一个工作表sheet1
try
{
#region
Excel.Range rngFoundFirst = null; //工作表范围定义，查找开始位置
Excel.Range rng; //工作表范围定义
Excel.Range rngfind; //工作表范围定义,找到的位置
//--------XU20090524
Excel.Range rng1 = ws.get_Range("A1", Type.Missing);
if (
(!(rng1.Value == null || rng1.Value.ToString() == "") && rng1.Value.ToString() == "^|^")
||
(!(rng1.Value2 == null || rng1.Value2.ToString() == "") && rng1.Value2.ToString() == "^|^")
)
{
HPageBreaks = true;
rng1.Value2 = "";
rng1.Value = "";
}
//--------XU20090524
#region 生成多个sheet
if (TSWorkAppText != null) TSWorkAppText.Text = "填充报表数据...";
//------------------------------------------------------------生成多个sheet_begin(如果主表是多条记录就生成多个sheet)
rng = ws.get_Range("A1", "AZ100");//查找范围，在这个范围内
rngfind = rng.Find("#WT_", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing); //执行查找
//rngfind = rng.Find("#.#", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing); //执行查找
string[] datatables = new string[DS.Tables.Count];//标记是否已经清理过本表
//bool HasCopy = false;//是否已经执行复制表
//int ii = 0;
while (rngfind != null)
{
//检查是否搜索完成
if (rngFoundFirst == null)
{
rngFoundFirst = rngfind;
}
else if (rngfind.Row == rngFoundFirst.Row && rngfind.Column == rngFoundFirst.Column)
{
break;
}
//判断是否主表
rngvalue = rngfind.Value2.ToString();
izb = rngvalue.IndexOf("#.#");
if (izb > 0)
{
//获取主表名称和主表中的记录数量根据记录数量复制sheet
tbname = rngvalue.Substring(1, izb - 1);
if (DS.Tables.IndexOf(tbname) != -1 && datatables[DS.Tables.IndexOf(tbname)] == null)
{
datatables[DS.Tables.IndexOf(tbname)] = "1"; //表示已经处理过了
DataRow[] DRS_Del = DS.Tables[tbname].Select("(WFP_SEL is null or CONVERT(WFP_SEL ,'System.String')<>'1' and CONVERT(WFP_SEL ,'System.String')<>'true') and (WFP_NOW<>'1' or WFP_NOW is null or CONVERT(WFP_SEL ,'System.String')='false')");
foreach (DataRow DR in DRS_Del)
{
DS.Tables[tbname].Rows.Remove(DR);
}
//throw new Exception("提醒下" + DS.Tables[tbname].Rows.Count.ToString());
tbrows = DS.Tables[tbname].Rows.Count > tbrows ? DS.Tables[tbname].Rows.Count : tbrows; //取最大的一个父亲作为sheet的个数
//break;
}
//else
//{
//tbname = "";//不能设置为空，下面还要用表名来过滤子表（所以下面过滤子表使用的是最后一个父亲表）
//}
}
//ii++;
rngfind = rng.FindNext(rngfind);
}
//if (!HasCopy) //只复制一次,根据第一个主表进行添加sheet
//{
//throw new Exception("提醒下sheet数量:" + tbrows.ToString());
for (int i = 1; i <= tbrows; i++) //〈＝表示多添加了一个合计sheet(保存所有sheet的合计)
{
//如果只有１个sheet就不添加了
if (tbrows > 1) ws.Copy(System.Type.Missing, app.ActiveWorkbook.Sheets[i]); //复制当前工作表
}
// HasCopy = true;
//}
rng = null;
rngfind = null;
//--------------------------------------------------生成多个sheet_end
#endregion
#region 填写数据
/*
* 逐个sheet填写，查找所有带标识的单元格，并根据单元格中的数据判断是主表或者子表
* 主表直接填写数据
* 子表查找，先确定主表中的关键字，确定对应关键字中对应行的子表的数据
*
*/
string tbzname = ""; //子表名
string datakey = "";//过滤子表搜索条件
DataRow[] dr;//子表中的数据
//throw new Exception("提醒下" + tbrows.ToString());
int temp_num = 0;
int temp_temp1 = 0;
----------替换sheet的名字(准备数据)
ws = (Excel.Worksheet)wb.Worksheets[1];
string SheetNameTableName = "";
string SheetNameFieldName = "";
if (ws.Name.IndexOf("#WT_") == 0 && ws.Name.IndexOf("#.#") >= 0)
{
SheetNameTableName = tbqname = ws.Name.Substring(1, ws.Name.IndexOf("#.#") - 1);
SheetNameFieldName = ws.Name.Substring(ws.Name.IndexOf("#.#") + 3, ws.Name.Length - ws.Name.IndexOf("#.#") - 4);
}
----------替换sheet的名字
for (int i = 1; i < tbrows + 1; i++) //便历每个sheet进行替换
{
temp_num++;
datakey = "";
ws = (Excel.Worksheet)wb.Worksheets[i]; //指定sheet
-----------替换sheet的名字
if (SheetNameTableName != "")
{
if (DS.Tables.IndexOf(SheetNameTableName) != -1 && i > DS.Tables[SheetNameTableName].Rows.Count && DS.Tables[SheetNameTableName].Rows.Count > 0 && DS.Tables[SheetNameTableName].Columns.Contains(SheetNameFieldName)) //如果多个父亲的选中的行不一致，按最后一条记录处理
ws.Name = DS.Tables[tbqname].Rows[DS.Tables[tbqname].Rows.Count - 1][tbfied].ToString() + "(" + i.ToString() + "," + tbrows.ToString() + ")";
else if (DS.Tables.IndexOf(SheetNameTableName) != -1 && DS.Tables[SheetNameTableName].Rows.Count > 0 && DS.Tables[SheetNameTableName].Columns.Contains(SheetNameFieldName))
ws.Name = DS.Tables[SheetNameTableName].Rows[i - 1][SheetNameFieldName].ToString() + "(" + i.ToString() + "," + tbrows.ToString() + ")";
}
-----------替换sheet的名字
//替换主表的内容
rng = ws.UsedRange;// ws.get_Range("A1", "AZ100"); //查找范围
rngfind = rng.Find("#WT_", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing);
rngFoundFirst = null;
int temp_num1 = 0;
string rngfind_str = "";
string str_LastWT = "";
while (rngfind != null)
{
temp_num1++;
if (rngFoundFirst == null)
{
rngFoundFirst = rngfind;
}
else if (rngfind.Row == rngFoundFirst.Row && rngfind.Column == rngFoundFirst.Column)
{
//throw new Exception("提醒下:可以到这里");
break;
}
tbzname = "";
rngvalue = rngfind.Value2.ToString();
//if (rngfind.Value2 != null) { rngfind.Value = "test"; rngfind.Value2 = "test"; }
izb = rngvalue.IndexOf("#.#");
if (izb >= 0) //主表
{
//主表数据填写
tbqname = rngvalue.Substring(1, izb - 1);
tbfied = rngvalue.Substring(izb + 3, rngvalue.Length - izb - 4);
if (DS.Tables.IndexOf(tbqname) != -1)
{
if (DS.Tables[tbqname].Columns.IndexOf(tbfied) != -1)
{
//重置起点单元格
if (rngFoundFirst == rngfind)
{
rngFoundFirst = null;
}
//获取指标的权限配置
if (myworkFieldsets.Tables.Contains(tbqname) == false)
{
DataTable mydtfset = getWorkFieldSet(tbqname, wfpuser);
mydtfset.TableName = tbqname;
myworkFieldsets.Tables.Add(mydtfset.Copy());
}
if (i > DS.Tables[tbqname].Rows.Count && DS.Tables[tbqname].Rows.Count > 0) //如果多个父亲的选中的行不一致，按最后一条记录处理
{
rngfind.Value2 = getTableValue(DS.Tables[tbqname], myworkFieldsets.Tables[tbqname], tbfied, DS.Tables[tbqname].Rows.Count - 1);
//rngfind.Value2 = DS.Tables[tbqname].Rows[DS.Tables[tbqname].Rows.Count - 1][tbfied].ToString();
}
else if (DS.Tables[tbqname].Rows.Count > 0)
{
rngfind.Value2 = getTableValue(DS.Tables[tbqname], myworkFieldsets.Tables[tbqname], tbfied, i - 1);
//rngfind.Value2 = DS.Tables[tbqname].Rows[i - 1][tbfied].ToString();
}
if (tbfied == "图片" || tbfied == "照片" || tbfied == "IMG" || tbfied == "PIC")
{
SetCellPic(ws, rngfind);
}
}
}
}
else if (str_LastWT != rngvalue)//子表
{
temp_temp1++;
str_LastWT = rngfind.Value2.ToString();
if (rngfind.Value2 != null) rngfind.Value2 = "";
tbzname = rngvalue.Substring(1, rngvalue.Length - 2);//子表名称
//throw new Exception("提醒下子表名:" + DS.Tables.IndexOf(tbzname).ToString());
//子表数据填写
if (DS.Tables.IndexOf(tbzname) != -1)//判断子表和主表是否存在
{
//--先把子表的这两列删除，因为子表的这两列没有用
if (DS.Tables[tbzname].Columns.Contains("WFP_SEL")) DS.Tables[tbzname].Columns.Remove("WFP_SEL");
if (DS.Tables[tbzname].Columns.Contains("WFP_NOW")) DS.Tables[tbzname].Columns.Remove("WFP_NOW");
#region 获取主表的关键列，确定子表的检索条件
if (tbname != "")
{
//throw new Exception("提醒下检索主表：" + tbname);
if (DS.Tables.IndexOf(tbname) != -1 && DS.Tables[tbname].PrimaryKey.Length > 0)
{
//存在关键列的情况
foreach (DataColumn dc in DS.Tables[tbname].PrimaryKey)
{
if (DS.Tables[tbzname].Columns.IndexOf(dc.ColumnName) != -1 && dc.ColumnName.ToLower() != "objid" && dc.ColumnName.ToLower() != "parentid" && DS.Tables[tbname].Rows.Count > 0)
{
if (dc.DataType.ToString() == "System.Boolean")
{
datakey += " and " + dc.ColumnName + "='"
+ (DS.Tables[tbname].Rows[i - 1][dc.ColumnName].ToString() == "True" ? "1" : "0") + "'";
}
else
{
datakey += " and " + dc.ColumnName + "='"
+ DS.Tables[tbname].Rows[i - 1][dc.ColumnName].ToString() + "'";
}
}
}
}
else
{
//不存在关键列的情况查找
if (DS.Tables.IndexOf(tbname) != -1)
for (int tj = 0; tj < DS.Tables[tbname].Columns.Count; tj++)
{
for (int tk = 0; tk < DS.Tables[tbzname].Columns.Count; tk++)
{
if (DS.Tables[tbname].Columns[tj].ColumnName == DS.Tables[tbzname].Columns[tk].ColumnName)
{
datakey += " and " + DS.Tables[tbname].Columns[tj].ColumnName
+ " = '" + DS.Tables[tbname].Rows[i - 1][tj].ToString() + "'";
}
}
}
}
}
#endregion
//throw new Exception("临时中断...");
#region 填充子表数据
//if (DS.Tables[tbzname].Columns.Contains("WFP_SEL")) DS.Tables[tbzname].Columns.Remove("WFP_SEL");
//if (DS.Tables[tbzname].Columns.Contains("WFP_NOW")) DS.Tables[tbzname].Columns.Remove("WFP_NOW");
//throw new Exception("提醒下检索关键字：" + datakey);
//获取指标的权限配置
if (myworkFieldsets.Tables.Contains(tbzname) == false)
{
DataTable mydtfset = getWorkFieldSet(tbzname, wfpuser);
mydtfset.TableName = tbzname;
myworkFieldsets.Tables.Add(mydtfset.Copy());
}
if (datakey != "")
{
dr = DS.Tables[tbzname].Select(datakey.Substring(5, datakey.Length - 5));
}
else
{
dr = DS.Tables[tbzname].Select("1=1");
}
dr = getDataRowValue(DS.Tables[tbzname], myworkFieldsets.Tables[tbzname], dr);
//tempstr = tempstr + dr.Length.ToString();
if (puttype == 1)//插入模式还是替换模式
{//插入行
//throw new Exception("提醒下子表长度:" + dr.Length.ToString());
for (int j = 1; j < dr.Length; j++)
{
rngfind.EntireRow.Copy(System.Type.Missing);//复制查找到的单元格所在的行
//rngfind.EntireRow.Copy(System.Type.Missing);//复制查找到的单元格所在的行
//if (dr.Length > 0) {
// rngfind.EntireRow.Select();
//}
//try //---------------------注意，这里当父亲表是多条记录的时候会导致意外错误，目前先屏蔽本语句以后确定修改
//{
// rngfind.EntireRow.Select();//选择该行,如果不使用该方面会出现插入两行的情况
//}catch{}
//插入复制行,如果之前没有使用COPY方面，将直接插入新行,j是第几条记录,确定插入行的位置
rngfind.get_Offset(j, 0).EntireRow.Insert(System.Type.Missing);
//rngfind.EntireRow.Insert(System.Type.Missing);
}
}
//遍历所有行的数据
//-------------------------------------优化考虑一次性复制
int rownum = dr.Length;
int colnum = DS.Tables[tbzname].Columns.Count;
//---------------------查找需要跳过的合计行begin
string hejiColumns = ","; //保存哪些行需要跳过不粘贴,1,2,5,5,
int hejiNum = 0;//合计的总列数
int gs = 0;
//下面发生的错误
try
{
for (int k = 0; k < colnum; k++)
{
//rngfind.get_Offset(0, k).MergeArea.Column 获取合并单元格的列坐标
//通过与上一个单元格的合并单元格列坐标比较确定是否是同一个单元格区域
if (gs != 0 && (rngfind.get_Offset(0, gs).MergeArea.Column == rngfind.get_Offset(0, gs - 1).MergeArea.Column))
{
hejiColumns = hejiColumns + gs + ",";
hejiNum++;
k = k - 1;
}
gs++;
}
}
catch
{
throw new Exception("异常中断(" + gs + "." + hejiNum + ")");
}
//---------------------查找需要跳过的合计行end
if (rownum > 0 && colnum > 0)
{
Excel.Range Temprngfind = ws.get_Range(rngfind.get_Offset(0, 0), rngfind.get_Offset(rownum - 1, colnum + hejiNum - 1));
object[,] objData = new Object[rownum, colnum + hejiNum];
for (int j = 0; j < rownum; j++)
{
gs = 0;
for (int k = 0; k < colnum; k++)
{
if (hejiColumns.IndexOf("," + gs.ToString() + ",") >= 0)
{
objData[j, gs] = "";
k--;
}
else
{
objData[j, gs] = dr[j][k].ToString();
}
gs++;
}
}
Temprngfind.Value = objData;
}
//---------------------------------------------------优化考虑一次性复制
//----------------------下面语句隐藏原因：太慢，因为一一单元格子一个的处理(单只能插入方式的添加数据了)
//int gs; //标记
//for (int j = 0; j < dr.Length; j++)
//{
// gs = 0;
// for (int k = 0; k < DS.Tables[tbzname].Columns.Count; k++)
// {
// //rngfind.get_Offset(0, k).MergeArea.Column 获取合并单元格的列坐标
// //通过与上一个单元格的合并单元格列坐标比较确定是否是同一个单元格区域
// if ((rngfind.get_Offset(0, gs).MergeArea.Column != rngfind.get_Offset(0, gs - 1).MergeArea.Column)
// || gs == 0)
// {
// if (puttype == 0)
// {
// //复制第一行数据的格式
// rngfind.get_Offset(0, gs).MergeArea.Copy(System.Type.Missing);
// if (j > 0)//粘贴到填写数据行
// ws.Paste(rngfind.get_Offset(j, gs), false);
// }
// if (!(dr[j][k] is DBNull))
// {
// rngfind.get_Offset(j, gs).Value2 = dr[j][k].ToString();
// }
// //else { rngfind.get_Offset(j, gs).Value2 = ""; }
// }
// else
// {
// k = k - 1;
// }
// gs = gs + 1;
// }
//}
//----------------------上面语句隐藏原因：太慢，因为一一单元格子一个的处理
//throw new Exception("提醒下子表长度:" + dr.Length.ToString());
//if (datakey != "")
//{
// dr = DS.Tables[tbzname].Select(datakey.Substring(5, datakey.Length - 5));
// //遍历所有行的数据
// for (int j = 0; j < dr.Length; j++)
// {
// for (int k = 0; k < DS.Tables[tbzname].Columns.Count; k++)
// {
// //复制第一行数据的格式
// rngfind.get_Offset(0, k).Copy(System.Type.Missing);
// ws.Paste(rngfind.get_Offset(j, k), false);
// if (!(dr[j][k] is DBNull))
// //{
// //}
// //else
// {
// rngfind.get_Offset(j, k).Value2 = dr[j][k].ToString();
// }
// }
// }
//}
//else { //没有设置关键字
// dr = DS.Tables[tbzname].Select("1=1");
// for (int j = 0; j < dr.Length; j++)
// {
// for (int k = 0; k < DS.Tables[tbzname].Columns.Count; k++)
// {
// //复制第一行数据的格式
// rngfind.get_Offset(0, k).Copy(System.Type.Missing);
// ws.Paste(rngfind.get_Offset(j, k), false);
// if (!(dr[j][k] is DBNull))
// //{
// //}
// //else
// {
// rngfind.get_Offset(j, k).Value2 = dr[j][k].ToString();
// }
// }
// }
//}
#endregion
}
}
if (rngfind.Value2 != null)
rngfind_str = rngfind_str + "|" + rngfind.Value2.ToString();
else rngfind_str = rngfind_str + "|" + "空";
rngfind = rng.FindNext(rngfind);
if (rngfind != null && rngfind.Value2 != null)
rngfind_str = rngfind_str + "-" + rngfind.Value2.ToString();
else rngfind_str = rngfind_str + "-" + "00";
//rngfind = rng.Find("#WT_", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing);
//throw new Exception("提醒下找到#WT数:" + temp_num1.ToString());
if (temp_num1 > 25) break;
}
//throw new Exception("提醒下找到#WT名:（" + temp_temp1.ToString() + ")" + rngfind_str + temp_num1.ToString() + "{"+str_LastWT+"}");
//throw new Exception("提醒下找到#WT数:" + temp_num1.ToString());
if (TSWorkAppText != null) TSWorkAppText.Text = string.Format("填充报表数据第{0}项共{1}项", i, tbrows);
}
//throw new Exception("提醒下sheet数：" + temp_num.ToString());
#endregion
#region 添加合计页
-----------------添加合计页
if (TSWorkAppText != null) TSWorkAppText.Text = "整理报表数据....";
Excel.Worksheet NeedPasteSheet = null;
if (tbrows > 1)
{
ArrayList MyArrRow = new ArrayList();//XU20090524
NeedPasteSheet = (Excel.Worksheet)wb.Worksheets[tbrows + 1]; //指定合计sheet
NeedPasteSheet.Name = "联合页";
int MinPasteRowNum = 0;//目标sheet的目标区域开始行
int MaxPasteRowNum = 0;//目标sheet的目标区域结束行
//int MaxNeedPRowNum = 0;//合并后的sheet的总行数//XU20090524
for (int i = 1; i < tbrows + 1; i++) //便历每个sheet进行替换
{
#region
Excel.Worksheet NeedCopySheet = (Excel.Worksheet)wb.Worksheets[i];//要拷贝的工作表
int MaxRowNum = NeedCopySheet.UsedRange.Rows.Count; //+ 1
int MaxColNum = NeedCopySheet.UsedRange.Columns.Count;//获得列数 + 1
//-XU20090524begin
//RowHeight "1:1"表示第一行, "1:2"表示,第一行和第二行
//((Excel.Range)_workSheet.Rows["1:1", System.Type.Missing]).RowHeight = 100;
//ColumnWidth "A:B"表示第一列和第二列, "A:A"表示第一列
//((Excel.Range)_workSheet.Columns["A:B", System.Type.Missing]).ColumnWidth = 10;
//</summary>
if (MyArrRow.Count != MaxRowNum - 1)
{
MyArrRow.Clear();
for (int RowNum = 1; RowNum < MaxRowNum; RowNum++)
{
double RowHeight = (double)((Excel.Range)NeedCopySheet.Rows[(RowNum) + ":" + (RowNum), System.Type.Missing]).Height;
MyArrRow.Add((object)RowHeight);
}
}
//--XU20090524end
MaxPasteRowNum = MinPasteRowNum + MaxRowNum;
Excel.Range FirstCopyCell = NeedCopySheet.get_Range("A1", "A1");
Excel.Range NeedCopyRng = NeedCopySheet.get_Range(FirstCopyCell.get_Offset(0, 0), FirstCopyCell.get_Offset(MaxRowNum, MaxColNum)); //要进行复制的单元格区域
NeedCopyRng.Copy(System.Type.Missing); //进行拷贝
Excel.Range FirstPasteCell = NeedPasteSheet.get_Range("A1", "A1");
Excel.Range NeedPasteRng = NeedPasteSheet.get_Range(FirstPasteCell.get_Offset(MinPasteRowNum, 0), FirstPasteCell.get_Offset(MaxPasteRowNum, MaxColNum)); //要进行复制的单元格区域
NeedPasteSheet.Paste(NeedPasteRng, false);//进行粘贴
//-XU20090524begin
if ((MyArrRow.Count > 0) && (MaxRowNum > MyArrRow.Count))
{
int AddRow = MyArrRow.Count;
int rowcount = 0;
for (int RowNum = 0; RowNum < AddRow; RowNum++)
{
rowcount = MinPasteRowNum + RowNum;
((Excel.Range)NeedPasteSheet.Rows[(rowcount + 1) + ":" + (rowcount + 1), System.Type.Missing]).RowHeight = double.Parse(MyArrRow[RowNum].ToString());
}
}
MinPasteRowNum = MaxPasteRowNum + 1; //空一行
//--XU20090524end
MinPasteRowNum = MaxPasteRowNum; //不空行 空行就+ 1
// 在联合页中插入分页符,最后一页不插入
if (i < tbrows && HPageBreaks)
{
NeedPasteSheet.HPageBreaks.Add(NeedCopySheet.Cells[MinPasteRowNum + 1, 1]); //0521
}
#endregion
}
}
-----------------添加合计页
#endregion
System.IO.File.Delete(fullPath);
//wb.Saved = true;
wb.SaveAs(fullPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //.xlShared
wb.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);
wbs.Close();
app.Quit();
int generation = System.GC.GetGeneration(app);
//throw new Exception("提醒下" + tempstr);
//****************《释放所有资源》********************
if (rngfind != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(rngfind);
if (rng != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
if (rngFoundFirst != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(rngFoundFirst);
if (ws != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
if (wb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
if (wbs != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.GC.Collect(generation);
//GC.Collect();
//GC.WaitForPendingFinalizers();
//GC.Collect();
//****************《释放所有资源》********************
#endregion
}
catch (Exception cw)
{
//if (cw.InnerException != null)
//{
// WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n {1}\r\n{2} {3}", cw.InnerException.Message, cw.InnerException.StackTrace, FullPath, wfpuser), "WFPExcel");
//}
//else
// WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n {1}\r\n{2} {3}", cw.Message, cw.StackTrace, FullPath, wfpuser), "WFPExcel");
}
finally
{
try
{
wb.SaveAs(fullPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //.xlShared
wb.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);
wbs.Close();
app.Quit();
if (ws != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
if (wb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
if (wbs != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
GC.Collect();
}
catch { }
}
}
catch (Exception cw1)
{
//if (cw1.InnerException != null)
//{
// WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n{1}\r\n{2}\r\n{3}\r\n ", cw1.InnerException.Message, cw1.InnerException.StackTrace, FullPath, wfpuser), "WFPExcel");
//}
//else
//{
// //WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n{1}\r\n{2}\r\n{3}\r\n", cw1.Message, cw1.StackTrace, FullPath, wfpuser), "WFPExcel");
// WFPDataBase.WFPConnection.WriteErrLog(cw1.Message + "\r\n" + cw1.StackTrace.ToString() + "\r\n" + FullPath + "\r\n" + wfpuser, "WFPExcel");
//}
throw cw1;
}
finally
{
try
{
if (wbs != null)
wbs.Close();
if (app != null)
app.Quit();
if (wbs != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
GC.Collect();
}
catch { }
}
#endregion
}
catch (Exception cw2)
{
//if (cw2.InnerException != null)
//{
// WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n {1}\r\n{2}\r\n {3} ", cw2.InnerException.Message, cw2.InnerException.StackTrace, FullPath, wfpuser), "WFPExcel");
//}
//else
// WFPDataBase.WFPConnection.WriteErrLog(String.Format("{0}\r\n {1}\r\n{2}\r\n {3} ", cw2.Message, cw2.StackTrace, FullPath, wfpuser), "WFPExcel");
throw cw2;
}
finally
{
try
{
if (app != null)
{
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
catch { }
}
}
catch (Exception cw4)
{
throw cw4;
}
}
传递的excel文件，存放文件的路径，数据集，提示框
6370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
