public int ExportExcel(string fileName)
{
///启动Excel
Cursor.Current = Cursors.WaitCursor; Excel.Application app = null ;
try
{
app = new Excel.Application();
}
catch
{
} if (app == null)
{
MessageBox.Show("启动Excel失败!","失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 1;
} Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
///添加worksheet
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1); if (worksheet == null)
{
MessageBox.Show("启动Excel失败!","失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 2;
} if (DataGridSource.TableStyles.Count > 0)
{
//写标题行
int row = 1;
for (int ii = 0 ; ii<DataGridSource.TableStyles[0].GridColumnStyles.Count ;ii ++)
{
if (DataGridSource.TableStyles[0].GridColumnStyles[ii].Width > 0)
{
worksheet.Cells[row,ii+1] = DataGridSource.TableStyles[0].GridColumnStyles[ii].HeaderText ;
}
//DrawBoxString(e ,font,System.Drawing.Brushes.LightGray ,DataGridSource.TableStyles[0].GridColumnStyles[ii].HeaderText ,1 ,1 ,newWidth[ii] );
} //写表格内容
DataView GridDV = null;
if (DataGridSource.DataSource.GetType().Name == "DataTable")
{
GridDV = ((System.Data.DataTable) DataGridSource.DataSource).DefaultView ;
}
if (DataGridSource.DataSource.GetType().Name == "DataView")
{
GridDV = (DataView)DataGridSource.DataSource;
}
for (int ii = 0 ;ii < GridDV.Table.Rows.Count ;ii++)
{
row ++ ;
for (int jj = 0 ; jj<DataGridSource.TableStyles[0].GridColumnStyles.Count ;jj ++)
{
if (DataGridSource.TableStyles[0].GridColumnStyles[jj].Width > 0)//需要判断DBNull
{
int index=0;
string substr="";
substr = " 00:00:00";
string content = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString();
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
{
substr = " 0:00";
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
worksheet.Cells[row,jj+1] = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString() ;
}
}
}
}
}
else
{
System.Data.DataTable dt = (System.Data.DataTable)(DataGridSource.DataSource) ;
//写标题行
int row = 1;
for (int ii = 0 ; ii<dt.Columns.Count ;ii ++)
{
worksheet.Cells[row,ii+1] = dt.Columns[ii].Caption ;
} //写表格内容
DataView GridDV = null;
if (DataGridSource.DataSource.GetType().Name == "DataTable")
{
GridDV = ((System.Data.DataTable) DataGridSource.DataSource).DefaultView ;
}
if (DataGridSource.DataSource.GetType().Name == "DataView")
{
GridDV = (DataView)DataGridSource.DataSource;
}
for (int ii = 0 ;ii < GridDV.Table.Rows.Count ;ii++)
{
row ++ ;
for (int jj = 0 ; jj<dt.Columns.Count ;jj ++)
{
int index=0;
string substr="";
substr = " 00:00:00";
string content = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString();
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
{
substr = " 0:00";
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
worksheet.Cells[row,jj+1] = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString() ;
}
}
}
} ///保存数据
worksheet.SaveAs(fileName ,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value ); ///结束进程
try
{
System.Threading.Thread.Sleep(100);
}
catch(COMException ex)
{
MessageBox.Show(ex.ToString(),"失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 3;
} try
{
// If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely
workbook.Saved = true;
app.UserControl = false;
app.Quit();
// 关闭进程,保留用户自启动的进程
Process [] localByName = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach(Process excelapp in localByName)
{
if((int)excelapp.MainWindowHandle==0)
{
excelapp.Kill();
excelapp.Close();
}
}
}
catch (COMException ex)
{
MessageBox.Show(ex.ToString(),"失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 4;
} Cursor.Current = Cursors.Default;
return 0;
}
{
///启动Excel
Cursor.Current = Cursors.WaitCursor; Excel.Application app = null ;
try
{
app = new Excel.Application();
}
catch
{
} if (app == null)
{
MessageBox.Show("启动Excel失败!","失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 1;
} Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
///添加worksheet
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1); if (worksheet == null)
{
MessageBox.Show("启动Excel失败!","失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 2;
} if (DataGridSource.TableStyles.Count > 0)
{
//写标题行
int row = 1;
for (int ii = 0 ; ii<DataGridSource.TableStyles[0].GridColumnStyles.Count ;ii ++)
{
if (DataGridSource.TableStyles[0].GridColumnStyles[ii].Width > 0)
{
worksheet.Cells[row,ii+1] = DataGridSource.TableStyles[0].GridColumnStyles[ii].HeaderText ;
}
//DrawBoxString(e ,font,System.Drawing.Brushes.LightGray ,DataGridSource.TableStyles[0].GridColumnStyles[ii].HeaderText ,1 ,1 ,newWidth[ii] );
} //写表格内容
DataView GridDV = null;
if (DataGridSource.DataSource.GetType().Name == "DataTable")
{
GridDV = ((System.Data.DataTable) DataGridSource.DataSource).DefaultView ;
}
if (DataGridSource.DataSource.GetType().Name == "DataView")
{
GridDV = (DataView)DataGridSource.DataSource;
}
for (int ii = 0 ;ii < GridDV.Table.Rows.Count ;ii++)
{
row ++ ;
for (int jj = 0 ; jj<DataGridSource.TableStyles[0].GridColumnStyles.Count ;jj ++)
{
if (DataGridSource.TableStyles[0].GridColumnStyles[jj].Width > 0)//需要判断DBNull
{
int index=0;
string substr="";
substr = " 00:00:00";
string content = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString();
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
{
substr = " 0:00";
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
worksheet.Cells[row,jj+1] = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString() ;
}
}
}
}
}
else
{
System.Data.DataTable dt = (System.Data.DataTable)(DataGridSource.DataSource) ;
//写标题行
int row = 1;
for (int ii = 0 ; ii<dt.Columns.Count ;ii ++)
{
worksheet.Cells[row,ii+1] = dt.Columns[ii].Caption ;
} //写表格内容
DataView GridDV = null;
if (DataGridSource.DataSource.GetType().Name == "DataTable")
{
GridDV = ((System.Data.DataTable) DataGridSource.DataSource).DefaultView ;
}
if (DataGridSource.DataSource.GetType().Name == "DataView")
{
GridDV = (DataView)DataGridSource.DataSource;
}
for (int ii = 0 ;ii < GridDV.Table.Rows.Count ;ii++)
{
row ++ ;
for (int jj = 0 ; jj<dt.Columns.Count ;jj ++)
{
int index=0;
string substr="";
substr = " 00:00:00";
string content = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString();
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
{
substr = " 0:00";
index=content.IndexOf(substr);
if(index>0)
{
worksheet.Cells[row,jj+1] = content.Substring(0,index);
}
else
worksheet.Cells[row,jj+1] = GridDV.Table.Rows[ii][DataGridSource.TableStyles[0].GridColumnStyles[jj].MappingName].ToString() ;
}
}
}
} ///保存数据
worksheet.SaveAs(fileName ,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value ); ///结束进程
try
{
System.Threading.Thread.Sleep(100);
}
catch(COMException ex)
{
MessageBox.Show(ex.ToString(),"失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 3;
} try
{
// If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely
workbook.Saved = true;
app.UserControl = false;
app.Quit();
// 关闭进程,保留用户自启动的进程
Process [] localByName = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach(Process excelapp in localByName)
{
if((int)excelapp.MainWindowHandle==0)
{
excelapp.Kill();
excelapp.Close();
}
}
}
catch (COMException ex)
{
MessageBox.Show(ex.ToString(),"失败",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
Cursor.Current = Cursors.Default;
return 4;
} Cursor.Current = Cursors.Default;
return 0;
}
解决方案 »
- [求解]Unicode字符“\uabcd”格式转换成汉字要先倒过来?
- WPF界面程序:能否单独隐藏标题栏上的图标?而不隐藏标题栏。
- 郁闷的GetOleDbSchemaTable...
- 键盘的删除键程序怎么做?谢谢高手帮忙啊!解决马上结帖!高分求救!谢谢啦!急急急!
- 初学C#,谁能给我解释下一元操作符什么意思(+和-)?????????
- Invoke or BeginInvoke cannot be called on a control until the window handle
- 寫asp.net時如何做即時查詢? TextBox輸入a則找出所有a開頭的記錄,輸入ab找出所有ab開頭的記錄
- 我是刚学C#的,简单问题请教
- 如何控制带编辑功能的DataGrid的TextBox的Width
- 淘宝的app数据抓取出错!提示:FAIL_SYS_PROTOVER_MISSED::缺少协议版本
- C# 中有没有Variant 类似的数据类型,有的话怎么用?
- 好不容易整三个小三角,今天上来一看,又成两个了:-(.还有一个问题.大家帮着看看.
worksheet.SaveAs(fileName ,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value );还有你用的导出方法是基于office的那个版本??
是在哪个命名空间下引用才好用的??