最近在做一家公司的奖佣金系统,用到了Excel报表导出问题.陈述下自己的实现方法.
首先创建个进度条的窗体,该窗体比较简单,放个进度条控件,加个属性 进度条最大的值.代码如下:
public
partial
class
viewProgressBarForm : Form
... {
private int m_totalSize; //Total Size
private int m_position; //Position
public viewProgressBarForm()...{
InitializeComponent();
}
public bool Increase(int nValue)
...{
if (nValue > 0)
...{
if (progressBar1.Value + nValue < progressBar1.Maximum)
...{
progressBar1.Value += nValue;
return true;
}
else
...{
progressBar1.Value = progressBar1.Maximum;
this.Close();
return false;
}
}
return false;
}
public int totalSize...{
set ...{m_totalSize = value;}
get ...{return m_totalSize;}
}
}
... {
private int m_totalSize; //Total Size
private int m_position; //Position
public viewProgressBarForm()...{
InitializeComponent();
}
public bool Increase(int nValue)
...{
if (nValue > 0)
...{
if (progressBar1.Value + nValue < progressBar1.Maximum)
...{
progressBar1.Value += nValue;
return true;
}
else
...{
progressBar1.Value = progressBar1.Maximum;
this.Close();
return false;
}
}
return false;
}
public int totalSize...{
set ...{m_totalSize = value;}
get ...{return m_totalSize;}
}
}
为了在显示进度条时候不影响窗体的响应.需要在使用进度条主窗体里建立委托方法.代码如下:
//
显示进度条委托
delegate void ShowProgressDelegate( int totalStep, int currentStep, string message);
void ShowProgress( int totalDigits, int digitsSoFar, string message)
... {
if (progressBarForm.getProgressBar().InvokeRequired == false)
...{
progressBarForm.totalSize=totalDigits;
progressBarForm.Increase(digitsSoFar);
progressBarForm.setMessage(message); //进度条上的中文信息
//检查是否已经取消
if (progressBarForm.isCancel)
...{
return;
}
//进查导入任务是否完成
if (digitsSoFar == totalDigits)
...{
progressBarForm.Close();
}
}
// Transfer control to correct thread
else
...{
ShowProgressDelegate showProgress = new ShowProgressDelegate(ShowProgress);
object inoutCancel = false; // Avoid boxing and losing our return value
Invoke(showProgress, new object[] ...{ totalDigits, digitsSoFar, message });
}
}
delegate void ShowProgressDelegate( int totalStep, int currentStep, string message);
void ShowProgress( int totalDigits, int digitsSoFar, string message)
... {
if (progressBarForm.getProgressBar().InvokeRequired == false)
...{
progressBarForm.totalSize=totalDigits;
progressBarForm.Increase(digitsSoFar);
progressBarForm.setMessage(message); //进度条上的中文信息
//检查是否已经取消
if (progressBarForm.isCancel)
...{
return;
}
//进查导入任务是否完成
if (digitsSoFar == totalDigits)
...{
progressBarForm.Close();
}
}
// Transfer control to correct thread
else
...{
ShowProgressDelegate showProgress = new ShowProgressDelegate(ShowProgress);
object inoutCancel = false; // Avoid boxing and losing our return value
Invoke(showProgress, new object[] ...{ totalDigits, digitsSoFar, message });
}
}
主要Excel导出方法:
public
void
exportExcel()
... {
Excel.ApplicationClass MyExcel = null
Excel.Workbooks MyWorkBooks = null;
Excel.Workbook MyWorkBook = null;
try
...{
MyExcel = new Excel.ApplicationClass();
MyWorkBooks = MyExcel.Workbooks;
MyExcel.Visible = false;
int n = 1;
foreach(object obj in list)...{
MyWorkBook = MyWorkBooks._Open(strFileName/**//*报表模版*/, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)MyWorkBook.Sheets[1];
xSheet.Cells[x, y] = obj.value;//设定值
xSheet.SaveAs(strSaveFileName/**//*保存路径*/, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
if (MyWorkBook != null) MyWorkBook.Close(false, Missing.Value, Missing.Value);
ShowProgress(totals, n, "文件: " + strSaveFileName + " ...");
n++;
}
}catch(Exception ex)...{
throw ex;
}finally...{
if (MyWorkBooks != null) MyWorkBooks.Close();
if (MyExcel != null) MyExcel.Quit();
}
}
... {
Excel.ApplicationClass MyExcel = null
Excel.Workbooks MyWorkBooks = null;
Excel.Workbook MyWorkBook = null;
try
...{
MyExcel = new Excel.ApplicationClass();
MyWorkBooks = MyExcel.Workbooks;
MyExcel.Visible = false;
int n = 1;
foreach(object obj in list)...{
MyWorkBook = MyWorkBooks._Open(strFileName/**//*报表模版*/, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)MyWorkBook.Sheets[1];
xSheet.Cells[x, y] = obj.value;//设定值
xSheet.SaveAs(strSaveFileName/**//*保存路径*/, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
if (MyWorkBook != null) MyWorkBook.Close(false, Missing.Value, Missing.Value);
ShowProgress(totals, n, "文件: " + strSaveFileName + " ...");
n++;
}
}catch(Exception ex)...{
throw ex;
}finally...{
if (MyWorkBooks != null) MyWorkBooks.Close();
if (MyExcel != null) MyExcel.Quit();
}
}