DataGridView和ListView导出Excel

/// <summary> /// 把dataGridView里面的数据保存成Excel /// </summary> /// <param name="pDataGridView"></param> public static void ExportToExcel(DataGridView pDataGridView) { string saveFileName = ""; //bool fileSaved = false; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //被点了取消 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 //写入标题 for (int i = 0; i < pDataGridView.ColumnCount; i++) { worksheet.Cells[1, i + 1] = pDataGridView.Columns[i].HeaderText; } //写入数值 for (int r = 0; r < pDataGridView.Rows.Count; r++) { for (int i = 0; i < pDataGridView.ColumnCount; i++) { worksheet.Cells[r + 2, i + 1] = pDataGridView.Rows[r].Cells[i].Value; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit(); if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); } catch (Exception ex) { MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message); } } xlApp.Quit(); GC.Collect();//强行销毁 DoNetCommon.Commons.ShowMessageBox.ShowInfoBox("导出Excel成功!"); } /// <summary> /// 把listView里面的数据保存成Excle /// </summary> /// <param name="lv"></param> public static void ExportToExcel(ListView pListView) { if (pListView.Items == null) return; string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd"); saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //这里直接删除，因为saveDialog已经做了文件是否存在的判断 if (File.Exists(saveFileName)) File.Delete(saveFileName); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel"); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; xlApp.Visible = false; //填充列 for (int i = 0; i < pListView.Columns.Count; i++) { worksheet.Cells[1, i + 1] = pListView.Columns[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Font.Bold = true; } //填充数据（这里分了两种情况，1：lv带CheckedBox，2：不带CheckedBox） //带CheckedBoxes if (pListView.CheckBoxes == true) { int tmpCnt = 0; for (int i = 0; i < pListView.Items.Count; i++) { if (pListView.Items[i].Checked == true) { for (int j = 0; j < pListView.Columns.Count; j++) { if (j == 0) { worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else { worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].SubItems[j].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } } tmpCnt++; } } } else //不带Checkedboxe { for (int i = 0; i < pListView.Items.Count; i++) { for (int j = 0; j < pListView.Columns.Count; j++) { if (j == 0) { worksheet.Cells[2 + i, j + 1] = pListView.Items[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else { worksheet.Cells[2 + i, j + 1] = pListView.Items[i].SubItems[j].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } } } } object missing = System.Reflection.Missing.Value; try { workbook.Saved = true; workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } catch (Exception e1) { MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + e1.Message); } finally { xlApp.Quit(); System.GC.Collect(); } MessageBox.Show("导出Excle成功！"); }