方法1
这种方法需要在本机安装了Office,然后在程序中调用Windows Office的组件。
首先需要包含的空间:
using Excel = Microsoft.Office.Interop.Excel;
下面是导出到Excel表格的响应函数,(m_table是界面上的DataGridView控件)
//导出到excel文件
private void button_ToExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = m_table.Copy();
int mun = dt.Rows.Count;
if (dt == null) return;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel");
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";
saveDia.Title = "导出为Excel文件";
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK
&& !string.Empty.Equals(saveDia.FileName))
{
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
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK);
}
}
方法二
前面的方法虽然能够成功导出Excel到本机,但是它需要Office的组件,在有些没有Office组件的机器上就不能使用了。因而使用最多的是第三方的组件,这里使用到的组件是NPOI。
首先添加空间
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
DataTable dt = new DataTable();
if (this.dgv_allItems.DataSource == null || ((DataTable)this.dgv_allItems.DataSource).Rows.Count == 0)
{
MessageBox.Show("当前没有数据!");
return;
} //dgv_allItems是界面上的DataGridView
if (this.dgv_allItems.SelectedRows.Count > 0)
{
dt = (this.dgv_allItems.DataSource as DataTable).Clone();
foreach (DataGridViewRow r in this.dgv_allItems.SelectedRows)
{
dt.Rows.Add((r.DataBoundItem as DataRowView).Row.ItemArray);
}
}
else
{
dt = (this.dgv_allItems.DataSource as DataTable).Clone();
foreach (DataGridViewRow r in this.dgv_allItems.Rows)
{
dt.Rows.Add((r.DataBoundItem as DataRowView).Row.ItemArray);
}
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "97-2003文档(*.xls)|*.xls|2007文档(*.xlsx)|*.xlsx";
if (DialogResult.OK == dlg.ShowDialog())
{
string suffix = dlg.FileName.Substring(dlg.FileName.LastIndexOf(".") + 1, dlg.FileName.Length - dlg.FileName.LastIndexOf(".") - 1);
if (suffix == "xls")
{
GetExcelClass.RenderToExcel(dt, dlg.FileName);
}
else
GetExcelClass.TableToExcelForXLSX(dt, dlg.FileName);
}
/// <summary>
/// 导出数据到excel2003中
/// </summary>
/// <param name="table"></param>
/// <param name="filename"></param>
/// <returns></returns>
public bool RenderToExcel(DataTable table,string filename)
{
MemoryStream ms = new MemoryStream();
using (table)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
// proBar.progressBar1.Value = proBar.progressBar1.Value+1;
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
try
{
SaveToFile(ms, filename);
MessageBox.Show("数据导出成功!");
return true;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
return false;
}
}
}
//////////////////////////////////////////////////////////////////////////
public void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
}
/// <summary>
/// 导出数据到excel2007中
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
/// <returns></returns>
public bool TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
try
{
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
MessageBox.Show("数据导出成功!");
return true;
}
catch(SystemException ex)
{
MessageBox.Show(ex.ToString());
return false;
}
}