dataset和Excel的导入导出! 首先读入一个book.xls文件 存入dataset中,并在最后一行添加一个元素(假设这个表只有一列,这个我会)之后,如何将新的dataset存入book.xls文件?网上看了好多都不对!请高手给出详细注释的代码!vs2008谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 一、Dataset ->Excel public static System.Data.DataSet ExcelToDataTable(string strExcelFileName, string strSheetName) { //源的定义 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties="Excel 8.0;HDR=YES;IMEX=1"";//"Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null); // string tableName=schemaTable.Rows[0][2].ToString().Trim(); //Sql语句 //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法 string strExcel = "select * from [sheet1$]"; //定义存放的数据表 DataSet ds = new DataSet(); //连接数据源 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //适配到数据源 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); adapter.Fill(ds, strSheetName); conn.Close(); return ds; //.Tables[strSheetName]; }二、Excel -> DataSet Excel.Application objExcel = new Excel.ApplicationClass(); Excel.Workbooks objBooks = null; Excel._Workbook objBook = null; Excel.Sheets objSheets = null; Excel._Worksheet objSheet = null; Excel.Range objRange = null; objExcel.DisplayAlerts = false ;//设置保存文件存在时不提示 object objOpt = System.Reflection.Missing.Value; objBooks = (Excel.Workbooks) objExcel.Workbooks ; objBook = (Excel._Workbook)(objBooks.Add(objOpt)) ; objSheets = (Excel.Sheets) objBook.Worksheets ; objSheet = (Excel._Worksheet)(objSheets.get_Item(1)) ; objSheet.Name = "sheet1" ; // this.getFileName(dlgSaveFile.FileName) ; object[] objTital = { "销售部门", "高级经理", "经理","零售店编号","零售店", "零售店级别" } ; objRange = objSheet.get_Range("A1", "F1") ; objRange.set_Value(objOpt, objTital) ; objBook.SaveAs(dlgSaveFile.FileName,objOpt,objOpt,objOpt,objOpt,objOpt,Excel.XlSaveAsAccessMode.xlNoChange, objOpt,objOpt,objOpt,objOpt,objOpt) ; objBook.Close(true,objOpt,objOpt) ; objBooks.Close() ; objExcel.Quit() ; 给你一个我自己写的代码吧,用range实现,导出速度非常快。vs.net2003下编译通过。在项目里添加excel引用就可以 //以DataSet- 导出Excel文件 public void ExportExcel() { //DataSet ds=this.ds;//取得dataGrid绑定的DataSet //ds已定义 不需要上面的代码 if(ds==null || ds.Tables[0].Rows.Count<=0) { MessageBox.Show(this,"没有要导出的数据!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); return; } string str_saveFileName=""; bool bool_fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName =this.Text; saveDialog.ShowDialog(); str_saveFileName=saveDialog.FileName; if(str_saveFileName.IndexOf(":")<0) return; //被点了取消 Excel.Application excelApp=new Excel.Application(); if(excelApp==null) { MessageBox.Show(this,"无法创建Excel文件,您的电脑可能未安装Excel!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); return; } Excel.Workbooks workbooks=excelApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.Name=this.Text; Excel.Range range; long totalCount=ds.Tables[0].Rows.Count; //写入字段(列名) for(int i=0;i<ds.Tables[0].Columns.Count;i++) { worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[1,i+1]; range.Interior.ColorIndex = 35; range.Font.Size=10; range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous; } // 创建缓存数据 object[,] objData = new object[totalCount + 1, ds.Tables[0].Columns.Count ]; for(int r=0;r<ds.Tables[0].Rows.Count;r++) { for(int i=0;i<ds.Tables[0].Columns.Count;i++) { objData[r,i]=ds.Tables[0].Rows[r][i]; } Application.DoEvents(); } // 写入Excel range = worksheet.get_Range(excelApp.Cells[2,1],excelApp.Cells[totalCount + 1,ds.Tables[0].Columns.Count]); range.NumberFormat="@";//设置单元格为文本格式 range.Font.Size=10; range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous; range.Value2 = objData; if(str_saveFileName!="") { try { workbook.Saved =true; workbook.SaveCopyAs(str_saveFileName); bool_fileSaved=true; } catch(Exception exp) { bool_fileSaved=false; MessageBox.Show(this,"导出文件时出错,文件可能正被打开!\n"+exp.Message,"提 //以DataSet- 导出Excel文件 public void ExportExcel() { //DataSet ds=this.ds;//取得dataGrid绑定的DataSet //ds已定义 不需要上面的代码 if(ds==null || ds.Tables[0].Rows.Count<=0) { MessageBox.Show(this,"没有要导出的数据!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); return; } string str_saveFileName=""; bool bool_fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName =this.Text; saveDialog.ShowDialog(); str_saveFileName=saveDialog.FileName; if(str_saveFileName.IndexOf(":")<0) return; //被点了取消 Excel.Application excelApp=new Excel.Application(); if(excelApp==null) { MessageBox.Show(this,"无法创建Excel文件,您的电脑可能未安装Excel!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning); return; } Excel.Workbooks workbooks=excelApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.Name=this.Text; Excel.Range range; long totalCount=ds.Tables[0].Rows.Count; //写入字段(列名) for(int i=0;i<ds.Tables[0].Columns.Count;i++) { worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[1,i+1]; range.Interior.ColorIndex = 35; range.Font.Size=10; range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous; } // 创建缓存数据 object[,] objData = new object[totalCount + 1, ds.Tables[0].Columns.Count ]; for(int r=0;r<ds.Tables[0].Rows.Count;r++) { for(int i=0;i<ds.Tables[0].Columns.Count;i++) { objData[r,i]=ds.Tables[0].Rows[r][i]; } Application.DoEvents(); } // 写入Excel range = worksheet.get_Range(excelApp.Cells[2,1],excelApp.Cells[totalCount + 1,ds.Tables[0].Columns.Count]); range.NumberFormat="@";//设置单元格为文本格式 range.Font.Size=10; range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous; range.Value2 = objData; if(str_saveFileName!="") { try { workbook.Saved =true; workbook.SaveCopyAs(str_saveFileName); bool_fileSaved=true; } catch(Exception exp) { bool_fileSaved=false; MessageBox.Show(this,"导出文件时出错,文件可能正被打开!\n"+exp.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Error); } } else { bool_fileSaved=false; } excelApp.Quit(); GC.Collect();//强行销毁 if(bool_fileSaved && System.IO.File.Exists(str_saveFileName)) System.Diagnostics.Process.Start(str_saveFileName); } } } else { bool_fileSaved=false; } excelApp.Quit(); GC.Collect();//强行销毁 if(bool_fileSaved && System.IO.File.Exists(str_saveFileName)) System.Diagnostics.Process.Start(str_saveFileName); } 关于Random()的问题 【版务帖】2009年5月C#版务帖 水晶报表的创建向导怎么用? 为什么会这样帮我看下 文件操作,类似 BlockCopy 功能 有关Web网页导出到Word中问题---导出后要分页 一个有点复杂的计算程序 哪里有现成的Winsock控件可以直接在C#中用,急急 excel数据导入access方法 请教两个问题 停车游戏如何找到停车路线?有5种颜色的汽车,停车位共6个。每种颜色汽车有对应颜色的停车位,停车位之间有的有通道,有的没有。最初5种颜色的汽车未停在对应车位上,设计程序找车停车路线。 为什么我的richTextBox 中显示的内容不全啊??
{
//源的定义
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strExcelFileName + ";" +
"Extended Properties="Excel 8.0;HDR=YES;IMEX=1"";//"Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
// string tableName=schemaTable.Rows[0][2].ToString().Trim();
//Sql语句
//string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
string strExcel = "select * from [sheet1$]"; //定义存放的数据表
DataSet ds = new DataSet();
//连接数据源
OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //适配到数据源
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName); conn.Close(); return ds; //.Tables[strSheetName];
}二、Excel -> DataSet Excel.Application objExcel = new Excel.ApplicationClass();
Excel.Workbooks objBooks = null;
Excel._Workbook objBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet objSheet = null;
Excel.Range objRange = null; objExcel.DisplayAlerts = false ;//设置保存文件存在时不提示
object objOpt = System.Reflection.Missing.Value;
objBooks = (Excel.Workbooks) objExcel.Workbooks ;
objBook = (Excel._Workbook)(objBooks.Add(objOpt)) ;
objSheets = (Excel.Sheets) objBook.Worksheets ;
objSheet = (Excel._Worksheet)(objSheets.get_Item(1)) ; objSheet.Name = "sheet1" ; // this.getFileName(dlgSaveFile.FileName) ; object[] objTital = { "销售部门", "高级经理", "经理","零售店编号","零售店", "零售店级别" } ;
objRange = objSheet.get_Range("A1", "F1") ;
objRange.set_Value(objOpt, objTital) ; objBook.SaveAs(dlgSaveFile.FileName,objOpt,objOpt,objOpt,objOpt,objOpt,Excel.XlSaveAsAccessMode.xlNoChange,
objOpt,objOpt,objOpt,objOpt,objOpt) ;
objBook.Close(true,objOpt,objOpt) ;
objBooks.Close() ;
objExcel.Quit() ;
public void ExportExcel()
{
//DataSet ds=this.ds;//取得dataGrid绑定的DataSet
//ds已定义 不需要上面的代码
if(ds==null || ds.Tables[0].Rows.Count<=0)
{
MessageBox.Show(this,"没有要导出的数据!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
}
string str_saveFileName="";
bool bool_fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName =this.Text;
saveDialog.ShowDialog();
str_saveFileName=saveDialog.FileName;
if(str_saveFileName.IndexOf(":")<0) return; //被点了取消
Excel.Application excelApp=new Excel.Application();
if(excelApp==null)
{
MessageBox.Show(this,"无法创建Excel文件,您的电脑可能未安装Excel!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
} Excel.Workbooks workbooks=excelApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name=this.Text;
Excel.Range range; long totalCount=ds.Tables[0].Rows.Count; //写入字段(列名)
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[1,i+1];
range.Interior.ColorIndex = 35;
range.Font.Size=10;
range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
} // 创建缓存数据
object[,] objData = new object[totalCount + 1, ds.Tables[0].Columns.Count ]; for(int r=0;r<ds.Tables[0].Rows.Count;r++)
{
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
objData[r,i]=ds.Tables[0].Rows[r][i];
}
Application.DoEvents();
} // 写入Excel
range = worksheet.get_Range(excelApp.Cells[2,1],excelApp.Cells[totalCount + 1,ds.Tables[0].Columns.Count]);
range.NumberFormat="@";//设置单元格为文本格式
range.Font.Size=10;
range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
range.Value2 = objData; if(str_saveFileName!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(str_saveFileName);
bool_fileSaved=true;
}
catch(Exception exp)
{
bool_fileSaved=false;
MessageBox.Show(this,"导出文件时出错,文件可能正被打开!\n"+exp.Message,"提 //以DataSet- 导出Excel文件
public void ExportExcel()
{
//DataSet ds=this.ds;//取得dataGrid绑定的DataSet
//ds已定义 不需要上面的代码
if(ds==null || ds.Tables[0].Rows.Count<=0)
{
MessageBox.Show(this,"没有要导出的数据!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
}
string str_saveFileName="";
bool bool_fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName =this.Text;
saveDialog.ShowDialog();
str_saveFileName=saveDialog.FileName;
if(str_saveFileName.IndexOf(":")<0) return; //被点了取消
Excel.Application excelApp=new Excel.Application();
if(excelApp==null)
{
MessageBox.Show(this,"无法创建Excel文件,您的电脑可能未安装Excel!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
} Excel.Workbooks workbooks=excelApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name=this.Text;
Excel.Range range; long totalCount=ds.Tables[0].Rows.Count; //写入字段(列名)
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[1,i+1];
range.Interior.ColorIndex = 35;
range.Font.Size=10;
range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
} // 创建缓存数据
object[,] objData = new object[totalCount + 1, ds.Tables[0].Columns.Count ]; for(int r=0;r<ds.Tables[0].Rows.Count;r++)
{
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
objData[r,i]=ds.Tables[0].Rows[r][i];
}
Application.DoEvents();
} // 写入Excel
range = worksheet.get_Range(excelApp.Cells[2,1],excelApp.Cells[totalCount + 1,ds.Tables[0].Columns.Count]);
range.NumberFormat="@";//设置单元格为文本格式
range.Font.Size=10;
range.Cells.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
range.Value2 = objData; if(str_saveFileName!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(str_saveFileName);
bool_fileSaved=true;
}
catch(Exception exp)
{
bool_fileSaved=false;
MessageBox.Show(this,"导出文件时出错,文件可能正被打开!\n"+exp.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
else
{
bool_fileSaved=false;
}
excelApp.Quit();
GC.Collect();//强行销毁
if(bool_fileSaved && System.IO.File.Exists(str_saveFileName)) System.Diagnostics.Process.Start(str_saveFileName);
}
}
}
else
{
bool_fileSaved=false;
}
excelApp.Quit();
GC.Collect();//强行销毁
if(bool_fileSaved && System.IO.File.Exists(str_saveFileName)) System.Diagnostics.Process.Start(str_saveFileName);
}