SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
DataSet ds=new DataSet();
da.Fill(ds,"table1");
DataTable dt=ds.Tables["table1"];
string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数
FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("自动编号,姓名,年龄");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.WriteFile(name); // 把文件流发送到客户端
Response.End();
public void Out2Excel(string sTableName,string url)
{
Excel.Application oExcel=new Excel.Application();
Workbooks oBooks;
Workbook oBook;
Sheets oSheets;
Worksheet oSheet;
Range oCells;
string sFile="",sTemplate="";
//
System.Data.DataTable dt=TableOut(sTableName).Tables[0];
sFile=url+"myExcel.xls";
sTemplate=url+"MyTemplate.xls";
//
oExcel.Visible=false;
oExcel.DisplayAlerts=false;
//定义一个新的工作簿
oBooks=oExcel.Workbooks;
oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);
oBook=oBooks.get_Item(1);
oSheets=oBook.Worksheets;
oSheet=(Worksheet)oSheets.get_Item(1);
//命名该sheet
oSheet.Name="Sheet1";
oCells=oSheet.Cells;
//调用dumpdata过程，将数据导入到Excel中去
DumpData(dt,oCells);
//保存
oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
oBook.Close(false, Type.Missing,Type.Missing);
//退出Excel，并且释放调用的COM资源
oExcel.Quit();
GC.Collect();
KillProcess("Excel");
}
private void KillProcess(string processName)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process();
//得到所有打开的进程
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if(!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch(Exception Exc)
{
throw new Exception("",Exc);
}
}
2、方法2：
复制代码 代码如下:
protected void ExportExcel()
{
gridbind();
if(ds1==null) return;
string saveFileName="";
// bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName ="Sheet1";
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0) return; //被点了取消
// excelapp.Workbooks.Open (App.path & 工程进度表.xls)
Excel.Application xlApp=new Excel.Application();
object missing=System.Reflection.Missing.Value;
if(xlApp==null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;
string oldCaption=Title_label .Text.Trim ();
long totalCount=ds1.Tables[0].Rows.Count;
long rowRead=0;
float percent=0;
worksheet.Cells[1,1]=Title_label .Text.Trim ();
//写入字段
for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
{
worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName;
range=(Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
Caption .Visible = true;
for(int r=0;r<ds1.Tables[0].Rows.Count;r++)
{
for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
{
worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r];
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;
this.Caption.Text= "正在导出数据["+ percent.ToString("0.00") +"%]...";
Application.DoEvents();
}
worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
this.Caption.Visible= false;
this.Caption.Text= oldCaption;
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
if(ds1.Tables[0].Columns.Count>1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
}
workbook.Close(missing,missing,missing);
xlApp.Quit();
}
三、附注：
虽然都是实现导出excel的功能，但在asp.net和winform的程序中，实现的代码是各不相同的。在asp.net中，是在服务器端读取数据，在服务器端把数据以ms-excel的格式，以Response输出到浏览器（客户端)；而在winform中，是把数据读到客户端（因为winform运行端就是客户端），然后调用客户端安装的office组件，将读到的数据写在excel
您可能感兴趣的文章: