问题描述:做一个项目,里面用到将资料导出到EXCEL,使用的是EXCEL.DLL,因为数据量比较大,如果用CELLS一个一个填充的话需要很久时间才能完成,故我采用的是将EXCEL做为DATATABLE来操作,但这样导出的EXCEL使用公式计算,比如SUM,得出的结果全为零,后来发现只要编辑一下单元格(双击单元格数据),该单元格就能参与公式计算了.这种问题甚是奇怪.
解决方法:只用放弃将EXCEL做为表来操作的方法.回到使用EXCEL组件来操作,不使用CELL一个一个填充,而是先选中要填充的范围,然后整个赋值,这种方法还是比较快的,这里只能使用FORMULAARRAY这个属性,才能使导出的EXCEL直接参与计算,不知为何?
以下为详细代码,以做MARK:
实现的功能:表头与数据来源可分开,生成EXCEL时可分别设置.
隐藏第一列,第2 3 4列合并相同的行.相同行的最后一行可设置颜色.
杀掉EXCEL进程.
string
FileFullPath
=
"
../Download/
"
+
Session[
"
userID
"
].ToString()
+
"
_DPS_
"
+
DateTime.Now.ToString(
"
yyyyMMdd_hhmmss
"
)
+
"
(本月).xls
"
;
DateTime start = DateTime.Now; // 記錄進程開始的時間間隔
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false ;
app.DisplayInfoWindow = false ;
DateTime end = DateTime.Now; // 記錄進程的結束時間間隔
Excel.Workbooks books = app.Workbooks;
try
{
Excel.Workbook book = books.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet) book.Sheets[ 1 ];
sheet.Name = " 2007-08-10 " ;
string SheetName = sheet.Name; // 工作表名稱
// 標題頭,在excel第一列寫入標題
string [] headdata = btnExcel.Attributes[ " headdata " ].Split( " | " .ToCharArray()); // 表头数据
for ( int i = 0 ; i < headdata.Length; i ++ )
{
Excel.Range range = (Excel.Range) sheet.Cells[ 1 , i + 1 ];
range.NumberFormatLocal = " @ " ; // 設定為文本格式
range.HorizontalAlignment = Excel.Constants.xlCenter;
range.set_Value(Missing.Value, headdata[i].Replace( " <br> " , " \r\n " ));
}
// 輸出標題頭結束
// 以下輸出數據列
ArrayList tmpRange = new ArrayList(); // 保存需要變色的行
string [] size = { "" , "" ,gridData.Items[ 0 ].Cells[ 2 ].Text.Trim(),gridData.Items[ 0 ].Cells[ 3 ].Text.Trim(),gridData.Items[ 0 ].Cells[ 4 ].Text.Trim()};
string [,] ss = null ; // 保存數據的二維數組
int row = 0 ,colCount = 0 ; // 有多少行數據,多少列
for ( int i = 0 ; i < gridData.Items.Count; i ++ )
{
Button button = (Button) gridData.Items[i].Cells[ 0 ].Controls[ 0 ];
string [] rowdata = button.Attributes[ " rowdata " ].Split( " | " .ToCharArray());
if (i == 0 )
{
row = gridData.Items.Count;
colCount = rowdata.Length;
ss = new string [row,colCount];
}
for ( int j = 0 ;j < rowdata.Length;j ++ )
{
ss[i,j] = rowdata[j].Replace( " " , "" ); // 將數據保存在二維數組中
}
for ( int h = 2 ;h < 5 ;h ++ ) // 將第2列到第4列相同行的列數值保存起來用於后面合并單元格
{
if (gridData.Items[i].Cells[h].Text != size[h] ) // 某個panelsize改變后變色
{
string rownum = "" ; // 行數不能超過10萬,不然合并會出錯的,可以通過下面添加0擴大範圍
switch ((i + 2 ).ToString().Length)
{
case 1 :
rownum = " 00000 " + (i + 2 ).ToString();
break ;
case 2 :
rownum = " 0000 " + (i + 2 ).ToString();
break ;
case 3 :
rownum = " 000 " + (i + 2 ).ToString();
break ;
case 4 :
rownum = " 00 " + (i + 2 ).ToString();
break ;
case 5 :
rownum = " 0 " + (i + 2 ).ToString();
break ;
}
string tmpValue = h.ToString() + " , " + rownum;
if ( ! tmpRange.Contains(tmpValue))
tmpRange.Add(tmpValue);
size[h] = gridData.Items[i].Cells[h].Text.Trim();
}
else
if ( i == (row - 1 ))
{
string rownum = "" ; // 行數不能超過10萬,不然合并會出錯的,可以通過下面添加0擴大範圍
switch ((i + 3 ).ToString().Length)
{
case 1 :
rownum = " 00000 " + (i + 3 ).ToString();
break ;
case 2 :
rownum = " 0000 " + (i + 3 ).ToString();
break ;
case 3 :
rownum = " 000 " + (i + 3 ).ToString();
break ;
case 4 :
rownum = " 00 " + (i + 3 ).ToString();
break ;
case 5 :
rownum = " 0 " + (i + 3 ).ToString();
break ;
}
string tmpValue = h.ToString() + " , " + rownum;
if ( ! tmpRange.Contains(tmpValue))
tmpRange.Add(tmpValue);
}
}
}
Excel.Range rng1 = (Excel.Range)sheet.Cells[ 2 , 1 ]; // 選中開始的第一個格
rng1 = rng1.get_Resize(row,colCount); // 選中要填充的範圍
rng1.NumberFormatLocal = " #,##0_ " ; // 千分號
rng1.FormulaArray = ss; // 好象必用此方法,導出的excel才能用公式計算,此法比cell填充效率快很多
// 輸出數據列結束
// 設置樣式
sheet.UsedRange.Columns.AutoFit();
Excel.Borders borders = ((Excel.Borders) sheet.UsedRange.Borders);
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = Excel.XlBorderWeight.xlHairline;
((Excel.Range) sheet.Columns[ " A:A " , Missing.Value]).Hidden = true ; // 隱藏第一列
// 凍結儲存格
((Excel.Range) sheet.Cells[ 2 , 6 ]).Select();
app.ActiveWindow.FreezePanes = true ;
// app.ActiveWorkbook.ExclusiveAccess(); // 去掉"共用",不能用
// 合并相同的列
tmpRange.Sort();
string [] ChangeColors = tmpRange.ToArray( typeof (System.String)) as string [];
int startrow = 2 ;
ArrayList tmpArray = new ArrayList();
foreach ( string index in ChangeColors)
{
int colNum = int .Parse(index.Split( " , " .ToCharArray())[ 0 ]); // 列數值
if ( ! tmpArray.Contains(colNum))
{
startrow = 2 ;
tmpArray.Add(colNum);
}
int rowNum = int .Parse(index.Split( " , " .ToCharArray())[ 1 ]); // 行數值
Excel.Range range = (Excel.Range)sheet.get_Range(sheet.Cells[startrow,colNum],sheet.Cells[rowNum - 1 ,colNum]);
string Value = ((Excel.Range)sheet.Cells[startrow,colNum]).Text.ToString() ;
range.ClearContents();
range.MergeCells = true ;
range.set_Value(Type.Missing,Value);
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
startrow = rowNum;
// 相同行的最后一行變色
// Excel.Range rng = (Excel.Range)sheet.Rows[rowNum-1,Type.Missing];
// rng.Interior.ColorIndex = 50;
}
// 保存成文件
book.SaveAs(Server.MapPath(FileFullPath), Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Missing.Value,Missing.Value, Missing.Value, Missing.Value);
book.Close( true , Missing.Value, Missing.Value);
#endregion
// 開始下載生成的文件
Response.Redirect(FileFullPath, true );
}
catch (Exception Err)
{
getMessage(Err.Message);
}
finally
{
app.Quit();
if (app != null )
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null ;
}
if (Server.MachineName.ToUpper() != DataOP.Instance.MachineName.ToUpper())
DataOP.Instance.KillExcelProcess(start,end);
GC.Collect();
}
DateTime start = DateTime.Now; // 記錄進程開始的時間間隔
Excel.Application app = new Excel.ApplicationClass();
app.Visible = false ;
app.DisplayInfoWindow = false ;
DateTime end = DateTime.Now; // 記錄進程的結束時間間隔
Excel.Workbooks books = app.Workbooks;
try
{
Excel.Workbook book = books.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet) book.Sheets[ 1 ];
sheet.Name = " 2007-08-10 " ;
string SheetName = sheet.Name; // 工作表名稱
// 標題頭,在excel第一列寫入標題
string [] headdata = btnExcel.Attributes[ " headdata " ].Split( " | " .ToCharArray()); // 表头数据
for ( int i = 0 ; i < headdata.Length; i ++ )
{
Excel.Range range = (Excel.Range) sheet.Cells[ 1 , i + 1 ];
range.NumberFormatLocal = " @ " ; // 設定為文本格式
range.HorizontalAlignment = Excel.Constants.xlCenter;
range.set_Value(Missing.Value, headdata[i].Replace( " <br> " , " \r\n " ));
}
// 輸出標題頭結束
// 以下輸出數據列
ArrayList tmpRange = new ArrayList(); // 保存需要變色的行
string [] size = { "" , "" ,gridData.Items[ 0 ].Cells[ 2 ].Text.Trim(),gridData.Items[ 0 ].Cells[ 3 ].Text.Trim(),gridData.Items[ 0 ].Cells[ 4 ].Text.Trim()};
string [,] ss = null ; // 保存數據的二維數組
int row = 0 ,colCount = 0 ; // 有多少行數據,多少列
for ( int i = 0 ; i < gridData.Items.Count; i ++ )
{
Button button = (Button) gridData.Items[i].Cells[ 0 ].Controls[ 0 ];
string [] rowdata = button.Attributes[ " rowdata " ].Split( " | " .ToCharArray());
if (i == 0 )
{
row = gridData.Items.Count;
colCount = rowdata.Length;
ss = new string [row,colCount];
}
for ( int j = 0 ;j < rowdata.Length;j ++ )
{
ss[i,j] = rowdata[j].Replace( " " , "" ); // 將數據保存在二維數組中
}
for ( int h = 2 ;h < 5 ;h ++ ) // 將第2列到第4列相同行的列數值保存起來用於后面合并單元格
{
if (gridData.Items[i].Cells[h].Text != size[h] ) // 某個panelsize改變后變色
{
string rownum = "" ; // 行數不能超過10萬,不然合并會出錯的,可以通過下面添加0擴大範圍
switch ((i + 2 ).ToString().Length)
{
case 1 :
rownum = " 00000 " + (i + 2 ).ToString();
break ;
case 2 :
rownum = " 0000 " + (i + 2 ).ToString();
break ;
case 3 :
rownum = " 000 " + (i + 2 ).ToString();
break ;
case 4 :
rownum = " 00 " + (i + 2 ).ToString();
break ;
case 5 :
rownum = " 0 " + (i + 2 ).ToString();
break ;
}
string tmpValue = h.ToString() + " , " + rownum;
if ( ! tmpRange.Contains(tmpValue))
tmpRange.Add(tmpValue);
size[h] = gridData.Items[i].Cells[h].Text.Trim();
}
else
if ( i == (row - 1 ))
{
string rownum = "" ; // 行數不能超過10萬,不然合并會出錯的,可以通過下面添加0擴大範圍
switch ((i + 3 ).ToString().Length)
{
case 1 :
rownum = " 00000 " + (i + 3 ).ToString();
break ;
case 2 :
rownum = " 0000 " + (i + 3 ).ToString();
break ;
case 3 :
rownum = " 000 " + (i + 3 ).ToString();
break ;
case 4 :
rownum = " 00 " + (i + 3 ).ToString();
break ;
case 5 :
rownum = " 0 " + (i + 3 ).ToString();
break ;
}
string tmpValue = h.ToString() + " , " + rownum;
if ( ! tmpRange.Contains(tmpValue))
tmpRange.Add(tmpValue);
}
}
}
Excel.Range rng1 = (Excel.Range)sheet.Cells[ 2 , 1 ]; // 選中開始的第一個格
rng1 = rng1.get_Resize(row,colCount); // 選中要填充的範圍
rng1.NumberFormatLocal = " #,##0_ " ; // 千分號
rng1.FormulaArray = ss; // 好象必用此方法,導出的excel才能用公式計算,此法比cell填充效率快很多
// 輸出數據列結束
// 設置樣式
sheet.UsedRange.Columns.AutoFit();
Excel.Borders borders = ((Excel.Borders) sheet.UsedRange.Borders);
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = Excel.XlBorderWeight.xlHairline;
((Excel.Range) sheet.Columns[ " A:A " , Missing.Value]).Hidden = true ; // 隱藏第一列
// 凍結儲存格
((Excel.Range) sheet.Cells[ 2 , 6 ]).Select();
app.ActiveWindow.FreezePanes = true ;
// app.ActiveWorkbook.ExclusiveAccess(); // 去掉"共用",不能用
// 合并相同的列
tmpRange.Sort();
string [] ChangeColors = tmpRange.ToArray( typeof (System.String)) as string [];
int startrow = 2 ;
ArrayList tmpArray = new ArrayList();
foreach ( string index in ChangeColors)
{
int colNum = int .Parse(index.Split( " , " .ToCharArray())[ 0 ]); // 列數值
if ( ! tmpArray.Contains(colNum))
{
startrow = 2 ;
tmpArray.Add(colNum);
}
int rowNum = int .Parse(index.Split( " , " .ToCharArray())[ 1 ]); // 行數值
Excel.Range range = (Excel.Range)sheet.get_Range(sheet.Cells[startrow,colNum],sheet.Cells[rowNum - 1 ,colNum]);
string Value = ((Excel.Range)sheet.Cells[startrow,colNum]).Text.ToString() ;
range.ClearContents();
range.MergeCells = true ;
range.set_Value(Type.Missing,Value);
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
startrow = rowNum;
// 相同行的最后一行變色
// Excel.Range rng = (Excel.Range)sheet.Rows[rowNum-1,Type.Missing];
// rng.Interior.ColorIndex = 50;
}
// 保存成文件
book.SaveAs(Server.MapPath(FileFullPath), Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Missing.Value,Missing.Value, Missing.Value, Missing.Value);
book.Close( true , Missing.Value, Missing.Value);
#endregion
// 開始下載生成的文件
Response.Redirect(FileFullPath, true );
}
catch (Exception Err)
{
getMessage(Err.Message);
}
finally
{
app.Quit();
if (app != null )
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null ;
}
if (Server.MachineName.ToUpper() != DataOP.Instance.MachineName.ToUpper())
DataOP.Instance.KillExcelProcess(start,end);
GC.Collect();
}