程式开发中导出数据到EXCEL中遇到的一个问题小解
问题描述:做一个项目,里面用到将资料导出到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();
}
