问题描述
publicvoidDataSetToExcel(DataSetdataSet,stringfilename){if(dataSet.Tables.Count==0){return;}try{//建立Excel对象Microsoft.Office.Interop.Excel.Applicationexcel=newMicrosoft.Office.Interop.Excel.Application();//excel.Application.Workbooks.Add(true);Microsoft.Office.Interop.Excel.Workbookworkbook=excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//excel.Visible=isShowExcle;//Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];Microsoft.Office.Interop.Excel.Rangerange;workbook=excel.Workbooks.Add(System.Reflection.Missing.Value);for(intk=0;k<dataSet.Tables.Count;k++){System.Data.DataTabledataTable=dataSet.Tables[k];introwNumber=dataTable.Rows.Count;//不包括字段名if(rowNumber!=0){intcolumnNumber=dataTable.Columns.Count;intcolIndex=0;worksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);worksheet.Name=dataSet.Tables[k].TableName;//生成字段名称foreach(DataColumncolindataTable.Columns){colIndex++;excel.Cells[1,colIndex]=col.ColumnName;range=(Range)worksheet.Cells[1,colIndex];range.Interior.ColorIndex=6;range.Font.Bold=true;}object[,]objData=newobject[rowNumber,columnNumber];for(intr=0;r<rowNumber;r++){for(intc=0;c<columnNumber;c++){objData[r,c]=dataTable.Rows[r][c];}//Application.DoEvents();}//写入Excelrange=worksheet.get_Range(excel.Cells[2,1],excel.Cells[rowNumber+1,columnNumber]);//range.NumberFormat="@";//设置单元格为文本格式range.Value2=objData;worksheet.get_Range(excel.Cells[2,1],excel.Cells[rowNumber+1,2]).NumberFormat="@";worksheet.get_Range(excel.Cells[2,1],excel.Cells[rowNumber+1,2]).NumberFormat="yyyy-m-dh:mm:ss";worksheet.Cells.VerticalAlignment=Microsoft.Office.Interop.Excel.Constants.xlCenter;worksheet.Cells.HorizontalAlignment=Microsoft.Office.Interop.Excel.Constants.xlCenter;worksheet.Columns.EntireColumn.AutoFit();}}workbook.SaveAs(filename);workbook.Close();excel.Quit();DialogResultbtn=MessageBox.Show(null,"文件“"+filename+"”保存成功。是否现在打开？","信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information);if(btn==DialogResult.Yes){ApplicationClassexcelApp=newApplicationClass();Workbookworkbook2=excelApp.Workbooks.Open(filename);excelApp.Visible=true;}//appExcel.Quit();GC.Collect();}catch(Exceptionex){MessageBox.Show(ex.ToString());}}
解决方案
解决方案二：
用这个：
解决方案三：
参考：