代码
protected
void
exportToExcelClient(IDictionary
<
string
,
string
>
map)
{
if (map.Count == 0 )
{
MessageBox.Show( " 请选择要导出的字段! " , " 提示 " , MessageBoxButtons.OK, MessageBoxIcon.Information);
return ;
}
pgbClient.Value = 0 ;
_excel = new Excel.Application();
Excel.Workbook wbook = _excel.Workbooks.Add(Missing.Value);
Excel.Worksheet wsheet = (Excel.Worksheet)_excel.ActiveSheet;
Excel.Range range = (Excel.Range)wsheet.Cells;
Excel.Range ran = null ;
string tofilepath = Application.StartupPath + " \\Excel_save\\ " ;
string tofilename = " customer.xls " ;
string sql = " SELECT " ;
ArrayList keyArr = new ArrayList();
foreach (KeyValuePair < string , string > entry in map)
{
sql += entry.Value + " , " ;
keyArr.Add(entry.Key);
}
sql = sql.Substring( 0 , sql.Length - 1 );
sql += " FROM tb_ClientInfo WHERE 1=1 " + condition;
// MessageBox.Show(sql);
DataSet myds = boperate.getds(sql, " tb_Client " );
DataTable dt = myds.Tables[ " tb_Client " ];
for ( int i = 0 ; i < keyArr.Count; i ++ )
{
ran = (Excel.Range)range[ 1 , i + 1 ];
ran.Font.Bold = true ;
ran.Font.Size = 10 ;
ran.Value2 = keyArr[i];
// ran.Columns.AutoFit();
NAR(ran);
pgbClient.Step = 10 / keyArr.Count;
pgbClient.Value += 10 / keyArr.Count;
}
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
for ( int j = 0 ; j < dt.Columns.Count; j ++ )
{
ran = (Excel.Range)range[i + 2 , j + 1 ];
ran.Value2 = dt.Rows[i][j];
ran.Font.Size = 10 ;
ran.Columns.AutoFit();
NAR(ran);
}
pgbClient.Step = 90 / dt.Rows.Count;
pgbClient.Value += 90 / dt.Rows.Count;
}
if ( ! Directory.Exists(tofilepath))
{
Directory.CreateDirectory(tofilepath);
}
if (File.Exists(tofilepath + tofilename))
{
File.Delete(tofilepath + tofilename);
}
wbook.SaveAs(tofilepath + tofilename, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbook.Close( false , Type.Missing, Type.Missing);
NAR(range);
NAR(wsheet);
_excel.Quit();
NAR(_excel);
pgbClient.Value = pgbClient.Maximum;
System.Threading.Thread.Sleep( 500 );
System.Diagnostics.Process.Start( " EXCEL.EXE " ,tofilepath + tofilename);
// this.Close();
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
private void NAR( object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally
{
o = null ;
}
}
{
if (map.Count == 0 )
{
MessageBox.Show( " 请选择要导出的字段! " , " 提示 " , MessageBoxButtons.OK, MessageBoxIcon.Information);
return ;
}
pgbClient.Value = 0 ;
_excel = new Excel.Application();
Excel.Workbook wbook = _excel.Workbooks.Add(Missing.Value);
Excel.Worksheet wsheet = (Excel.Worksheet)_excel.ActiveSheet;
Excel.Range range = (Excel.Range)wsheet.Cells;
Excel.Range ran = null ;
string tofilepath = Application.StartupPath + " \\Excel_save\\ " ;
string tofilename = " customer.xls " ;
string sql = " SELECT " ;
ArrayList keyArr = new ArrayList();
foreach (KeyValuePair < string , string > entry in map)
{
sql += entry.Value + " , " ;
keyArr.Add(entry.Key);
}
sql = sql.Substring( 0 , sql.Length - 1 );
sql += " FROM tb_ClientInfo WHERE 1=1 " + condition;
// MessageBox.Show(sql);
DataSet myds = boperate.getds(sql, " tb_Client " );
DataTable dt = myds.Tables[ " tb_Client " ];
for ( int i = 0 ; i < keyArr.Count; i ++ )
{
ran = (Excel.Range)range[ 1 , i + 1 ];
ran.Font.Bold = true ;
ran.Font.Size = 10 ;
ran.Value2 = keyArr[i];
// ran.Columns.AutoFit();
NAR(ran);
pgbClient.Step = 10 / keyArr.Count;
pgbClient.Value += 10 / keyArr.Count;
}
for ( int i = 0 ; i < dt.Rows.Count; i ++ )
{
for ( int j = 0 ; j < dt.Columns.Count; j ++ )
{
ran = (Excel.Range)range[i + 2 , j + 1 ];
ran.Value2 = dt.Rows[i][j];
ran.Font.Size = 10 ;
ran.Columns.AutoFit();
NAR(ran);
}
pgbClient.Step = 90 / dt.Rows.Count;
pgbClient.Value += 90 / dt.Rows.Count;
}
if ( ! Directory.Exists(tofilepath))
{
Directory.CreateDirectory(tofilepath);
}
if (File.Exists(tofilepath + tofilename))
{
File.Delete(tofilepath + tofilename);
}
wbook.SaveAs(tofilepath + tofilename, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbook.Close( false , Type.Missing, Type.Missing);
NAR(range);
NAR(wsheet);
_excel.Quit();
NAR(_excel);
pgbClient.Value = pgbClient.Maximum;
System.Threading.Thread.Sleep( 500 );
System.Diagnostics.Process.Start( " EXCEL.EXE " ,tofilepath + tofilename);
// this.Close();
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
private void NAR( object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally
{
o = null ;
}
}
NAR方法用于释放资源 , exportToExcelClient方法的IDictionary<string, string> map字典结构用于根据用户所选的数据字段来导出到excel。就是这样,C#生成excel挺方便的,C/S , B/S都能用 。