在以前的项目中,做这些类似的功能也挺多的,没保存好,现在在这里记下,会以后所用:
- /// <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();//强行销毁
- }
这些代码可以完成此功能,以后会继续完善!