C#导出Excel
ExcelHelper:
public class ExcelHelper
{
public static void Export(System.Data.DataTable dt, string filePath)
{
if (dt == null)
{
throw new Exception("数据表中无数据");
}
int eRowIndex = 1;
int eColIndex = 1;
int cols = dt.Columns.Count;
int rows = dt.Rows.Count;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application();//.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
try
{
//列名的处理
for (int i = 0; i < cols; i++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName;
eColIndex++;
}
//列名加粗显示
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[eRowIndex, cols]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Name = "Arial";
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Size = "10";
eRowIndex++;
for (int i = 0; i < rows; i++)
{
eColIndex = 1;
for (int j = 0; j < cols; j++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString();
eColIndex++;
}
eRowIndex++;
}
//控制单元格中的内容。
xlApp.Cells.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
xlBook.SaveCopyAs(filePath);
xlApp.Workbooks.Close();
}
catch
{
throw;
}
finally
{
xlApp.Quit();
//杀掉Excel进程。
GC.Collect();
}
}
/// <summary>
/// 将datatable导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void ExportToExcel(DataTable dt)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "xlsx files (*.xlsx)|*.xlsx|All Files(*.*)|*.*";
sfd.ShowDialog();
string filepath = sfd.FileName;
if (filepath == "" || filepath.Substring(filepath.LastIndexOf('.') + 1).ToLower() != "xlsx")
{
return;
}
else
{
try
{
ExcelHelper.Export(dt, filepath);
MessageBox.Show("导出成功!", "提示");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
{
public static void Export(System.Data.DataTable dt, string filePath)
{
if (dt == null)
{
throw new Exception("数据表中无数据");
}
int eRowIndex = 1;
int eColIndex = 1;
int cols = dt.Columns.Count;
int rows = dt.Rows.Count;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application();//.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
try
{
//列名的处理
for (int i = 0; i < cols; i++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName;
eColIndex++;
}
//列名加粗显示
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[eRowIndex, cols]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Name = "Arial";
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Size = "10";
eRowIndex++;
for (int i = 0; i < rows; i++)
{
eColIndex = 1;
for (int j = 0; j < cols; j++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString();
eColIndex++;
}
eRowIndex++;
}
//控制单元格中的内容。
xlApp.Cells.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
xlBook.SaveCopyAs(filePath);
xlApp.Workbooks.Close();
}
catch
{
throw;
}
finally
{
xlApp.Quit();
//杀掉Excel进程。
GC.Collect();
}
}
/// <summary>
/// 将datatable导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void ExportToExcel(DataTable dt)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "xlsx files (*.xlsx)|*.xlsx|All Files(*.*)|*.*";
sfd.ShowDialog();
string filepath = sfd.FileName;
if (filepath == "" || filepath.Substring(filepath.LastIndexOf('.') + 1).ToLower() != "xlsx")
{
return;
}
else
{
try
{
ExcelHelper.Export(dt, filepath);
MessageBox.Show("导出成功!", "提示");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
需要:
导入Microsoft.Office.Interop.Excel,并且其Embed Interop Types属性设置为false,否则可能引发“Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded”错误。