通过DataSet导出Excel

最新推荐文章于 2018-07-17 16:51:16 发布

sz_bdqn

最新推荐文章于 2018-07-17 16:51:16 发布

阅读量3.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：开发积累 C# Winform 文章标签： dataset excel exception null string float

本文链接：https://blog.youkuaiyun.com/sz_bdqn/article/details/5867552

开发积累同时被 3 个专栏收录

101 篇文章

订阅专栏

32 篇文章

订阅专栏

Winform

19 篇文章

订阅专栏

在以前的项目中，做这些类似的功能也挺多的，没保存好，现在在这里记下，会以后所用：

/// <summary> /// 通过DataSet导出Excel /// </summary> /// <param name="ds"></param> public static void ExporDataSetToExcel(DataSet ds) { if (ds == 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; //被点了取消 Excel.Application xlapp = new Excel.ApplicationClass(); 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=this.captiontext; long totalcount = ds.Tables[0].Rows.Count; long rowread = 0; float percent = 0; // worksheet.Cells[1,1]=this.captiontext; //写入字段 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 // this.captionvisible = true; for (int r = 0; r < ds.Tables[0].Rows.Count; r++) { for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = ds.Tables[0].Rows[r][i]; } rowread++; percent = ((float)(100 * rowread)) / totalcount; // this.captiontext = "正在导出数据["+ percent.tostring("0.00") +"%]"; System.Windows.Forms.Application.DoEvents(); } // this.captionvisible = false; // this.captiontext = oldcaption; range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 2, ds.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 (ds.Tables[0].Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if (savefilename != "") { try { workbook.Saved = true; workbook.SaveCopyAs(savefilename); filesaved = true; MessageBox.Show("文件已经成功导出..."); } catch (Exception ex) { filesaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message); } } else { filesaved = false; } xlapp.Quit(); GC.Collect();//强行销毁 }

这些代码可以完成此功能，以后会继续完善!