母丹的回答：有两个方法,一个是将datatable转为excel,一个是将DataGridView 导出为excel 将dataset导出为excel也很简单,遍历下dataset.tables. 呈现乱码的是我为自己的程序定制的,你可以不加. public static bool tableinfo_to_excel(System.Data.DataTable dt, string fileName, bool isShowExcle) { DataSet ds = get_dataset(dt); if (ds == null) { return false; } Microsoft.Office.Interop.Excel.application app = new Microsoft.Office.Interop.Excel.Application(); try { if (app == null) { return false; } app.Visible = isShowExcle; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //_Worksheet worksheet. Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //_Worksheet worksheet = (_Worksheet)sheets.Item(1); worksheet.Name = "惆豢"; if (worksheet == null) { return false; } int i_row = 0; foreach (DataRow dr in dt.Rows) { string sLen = ""; string s = (string)dr["heardtext"]; string[] s_headtext = s.Split(','); char H = (char)(64 + s_headtext.Length / 26); char L = (char)(64 + s_headtext.Length % 26); if (s_headtext.Length < 26) { sLen = L.ToString(); } else { sLen = H.ToString() + L.ToString(); } i_row += 1; string sTmp = sLen + i_row.ToString(); Range ranCaption = worksheet.get_Range(sTmp, "A" + i_row); //ranCaption.Merge(true); ranCaption.MergeCells = true; ranCaption.RowHeight = 30; //ranCaption.Font.Color=3; ranCaption.Font.Color = 255; ranCaption.Font.Bold = true; ranCaption.Font.Size = 14; ranCaption.Borders.Color = 255; //ranCaption.Borders.LineStyle = XlLineStyle.xlDouble; //ranCaption.Borders.Value = -4119; ranCaption.Borders.Weight = 3; //ranCaption.HorizontalAlignment=2; //object lkjsdklf=ranCaption.Orientation; //object lkjsdklf = ranCaption.Style; ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter;//懈笢 //ranCaption.Font.ColorIndex = 16777215; //ranCaption.Font.ColorIndex = -2000; //font_my.Bold =true; //font_my.Background=XlBackground.xlBackgroundOpaque; // ranCaption.AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatNone, 2, font_my, "Center", true, null, 80); ranCaption.Value2 = (string)dr["table_name"]; i_row += 1; sTmp = sLen + i_row.ToString(); ranCaption = worksheet.get_Range(sTmp, "A" + i_row); ranCaption.Value2 = s_headtext; ranCaption.Borders.Color = 0; ranCaption.HorizontalAlignment = XlHAlign.xlHAlignCenter; System.Data.DataTable s_dt = ds.Tables[(string)dr["table_name"]]; object[] obj = new object[s_dt.Columns.Count]; //int i_date_index=new int(); foreach (DataRow dr_jkdj in s_dt.Rows) { for (int l = 0; l < s_dt.Columns.Count; l++) { if (dr_jkdj[l].GetType() == typeof(DateTime)) { obj[l] = dr_jkdj[l].ToString(); string cell11 = sLen + ((int)(l + 1)).ToString(); string cell21 = "A" + ((int)(l + 1)).ToString(); Range ran1 = worksheet.get_Range(cell11, cell21); Range cellrange = (Range)ran1.Cells[0, l + 1]; cellrange.ColumnWidth = 14; } //蚚誧赻隅砱氝楼 else if (dr_jkdj[l].GetType() == typeof(Boolean)) { DateTime dt_jl = (DateTime)dr_jkdj[l - 2]; int i_day = (int)dr_jkdj[l - 1]; if (dt_jl.AddDays(i_day) < DateTime.Now) { obj[l] = "峎党闭?"; } else obj[l] = "淏婓峎党"; } //蚚誧赻隅砱氝楼赋旰 else obj[l] = dr_jkdj[l]; } string cell1 = sLen + ((int)(i_row + 1)).ToString(); string cell2 = "A" + ((int)(i_row + 1)).ToString(); Range ran = worksheet.get_Range(cell1, cell2); //cellrange.Columns=20; ran.Borders.Color = 0; i_row++; ran.Value2 = obj; } i_row = i_row + 2; } if (!isShowExcle) { workbook.Password = "18356771"; } workbook.SaveCopyAs(fileName); workbook.Saved = true; app.UserControl = false; app.Quit(); return true; } catch(Exception ee) { app.UserControl = false; app.Quit(); return false; } } public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle) { //膘蕾?????勤砓 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try { if (app == null) { return false; } app.Visible = isShowExcle; Workbooks workbooks = app.Workbooks; _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //_Workbook workbook1 = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //_Worksheet worksheet. Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //_Worksheet worksheet = (_Worksheet)sheets.Item(1); if (worksheet == null) { return false; } string sLen = ""; //?腕郔缀珨蹈蹈靡 char H = (char)(64 + gridView.ColumnCount / 26); char L = (char)(64 + gridView.ColumnCount % 26); if (gridView.ColumnCount < 26) { sLen = L.ToString(); } else { sLen = H.ToString() + L.ToString(); } //梓枙 string sTmp = sLen + "1"; Range ranCaption = worksheet.get_Range(sTmp, "A1"); string[] asCaption = new string[gridView.ColumnCount]; for (int i = 0; i < gridView.ColumnCount; i++) { asCaption[i] = gridView.Columns[i].HeaderText; } ranCaption.Value2 = asCaption; //杅擂 object[] obj = new object[gridView.Columns.Count]; for (int r = 0; r < gridView.RowCount; r++) { for (int l = 0; l < gridView.Columns.Count; l++) { if (gridView[l, r].ValueType == typeof(DateTime)) { obj[l] = gridView[l, r].Value.ToString(); } else { obj[l] = gridView[l, r].Value; } } string cell1 = sLen + ((int)(r + 2)).ToString(); string cell2 = "A" + ((int)(r + 2)).ToString(); Range ran = worksheet.get_Range(cell1, cell2); ran.Value2 = obj; } //怅湔 if (!isShowExcle) { workbook.Password = "18356771"; } workbook.SaveCopyAs(fileName); //workbook.SaveAs(fileName, ".xls", "18356771", false, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, false, false, false, false); workbook.Saved = true; } finally { //寿敕 app.UserControl = false; app.Quit(); } return true; } ぺ輕描淡寫﹎的回答：给你一个datagrid的代码： // 导出列表信息到excel public static void gsendgridinfotoexcel(datagrid gridx) { excel.application excel= new excel.applicationclass(); excel._workbook xbk = excel.workbooks.add(true); excel._worksheet xst = (excel._worksheet)xbk.activesheet; excel.range excelcell=null; try { //赋值对象 object[] objarr; datatable dttest=new datatable();; int i,j; int irows,icows; int ivisable; ivisable=0; icows=0; arraylist list=new arraylist(); //如果绑定数据源是datatable和dataset,取得行数 if (gridx.datasource is system.data.dataset || gridx.datasource is system.data.datatable) { dttest=(datatable)gridx.datasource; irows=dttest.rows.count; } else if (gridx.datasource is system.data.dataview) { dataview dvtest=(dataview)gridx.datasource; irows=dvtest.count; dttest=dvtest.table; } //如果是集合取得行数 else { system.collections.collectionbase coltest; coltest=(system.collections.collectionbase)gridx.datasource; irows=coltest.count; } //如果有tablestyles则根据tablestyles取得(标题行) if (gridx.tablestyles.count>0) { icows=gridx.tablestyles[0].gridcolumnstyles.count; for(i=0;i<icows;i++) { if(gridx.tablestyles[0].gridcolumnstyles[i].width>0) { ivisable++; list.add(gridx.tablestyles[0].gridcolumnstyles[i].headertext); } } objarr = new object[ivisable]; objarr=list.toarray(); excelcell = xst.get_range(excel.cells[1,1],excel.cells[1,ivisable]); excelcell.value2 = objarr; //数据行 for(i=0;i<irows;i++) { objarr = new object[ivisable]; list.clear(); for(j=0;j<icows;j++) { if(gridx.tablestyles[0].gridcolumnstyles[j].width>0) { list.add("'"+gridx[i,j].tostring().replace("\ ","")); } } if (list.equals(system.dbnull.value)) { break; } objarr=list.toarray(); excelcell = xst.get_range(excel.cells[i+2,1],excel.cells[i+2,ivisable]); excelcell.value2 = objarr; } } else { icows=dttest.columns.count; for(i=0;i<icows;i++) { list.add(dttest.columns[i].caption); } objarr = new object[icows]; objarr=list.toarray(); excelcell = xst.get_range(excel.cells[1,1],excel.cells[1,icows]); excelcell.value2 = objarr; //数据行 for(i=0;i<irows;i++) { objarr = new object[icows]; list.clear(); for(j=0;j<icows;j++) { list.add("'"+gridx[i,j].tostring().replace("\ ","")); } if (list.equals(system.dbnull.value)) { break; } objarr=list.toarray(); excelcell = xst.get_range(excel.cells[i+2,1],excel.cells[i+2,icows]); excelcell.value2 = objarr; } } dttest.dispose(); excel.visible=true; } catch (system.exception e) { throw e; } finally { excelcell=null; xbk=null; xst=null; excel=null; gc.collect(); } } } |
