先引用.NET中COM组件Microsoft Excel11.0 Object Libray
或者Microsoft Excel12.0 Object Libray或更高版本
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="isTitle">是否显示表头</param>
public static void OutputExcel(DataTable dt, bool isTitle)
{
Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
MyExcel.Application.Workbooks.Add(true);
Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
sheet.Name = "sheet1";
try
{
if (isTitle)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
MyExcel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//生成表头
}
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 12;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Borders.LineStyle = "1";
}
int Rows = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
Rows += 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
}
MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
}
sheet.Columns.AutoFit();//自动适应宽度
MyExcel.Visible = true;
}
catch (Exception) { }
finally { }
}
/// <summary>
/// 导出一定格式的Excle
/// </summary>
/// <param name="strTitle">标题</param>
/// <param name="dt">数据源</param>
/// <param name="isTitle">是否显示列名</param>
public static void OutputExcel(string strTitle, DataTable dt, bool isTitle)
{
Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
MyExcel.Application.Workbooks.Add(true);
Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
sheet.Name = "sheet1";
try
{
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).MergeCells = true;//合并Excle单元格
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true; //设置字体
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 20;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.Cells[1, 1] = strTitle;
if (isTitle)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
MyExcel.Cells[2, i + 1] = dt.Columns[i].ColumnName;//生成表头
}
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Bold = true;
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Size = 12;
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Borders.LineStyle = "1";
}
int Rows = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
Rows += 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
}
MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
}
sheet.Columns.AutoFit();//自动适应宽度
MyExcel.Visible = true;
}
catch (Exception) { }
finally { }
}
或者Microsoft Excel12.0 Object Libray或更高版本
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="isTitle">是否显示表头</param>
public static void OutputExcel(DataTable dt, bool isTitle)
{
Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
MyExcel.Application.Workbooks.Add(true);
Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
sheet.Name = "sheet1";
try
{
if (isTitle)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
MyExcel.Cells[1, i + 1] = dt.Columns[i].ColumnName;//生成表头
}
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 12;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Borders.LineStyle = "1";
}
int Rows = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
Rows += 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
}
MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
}
sheet.Columns.AutoFit();//自动适应宽度
MyExcel.Visible = true;
}
catch (Exception) { }
finally { }
}
/// <summary>
/// 导出一定格式的Excle
/// </summary>
/// <param name="strTitle">标题</param>
/// <param name="dt">数据源</param>
/// <param name="isTitle">是否显示列名</param>
public static void OutputExcel(string strTitle, DataTable dt, bool isTitle)
{
Excel.ApplicationClass MyExcel = new Excel.ApplicationClass();
MyExcel.Application.Workbooks.Add(true);
Excel._Worksheet sheet = (Excel._Worksheet)MyExcel.Workbooks[1].Worksheets[1];
sheet.Name = "sheet1";
try
{
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).MergeCells = true;//合并Excle单元格
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Bold = true; //设置字体
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).Font.Size = 20;
MyExcel.get_Range(MyExcel.Cells[1, 1], MyExcel.Cells[1, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.Cells[1, 1] = strTitle;
if (isTitle)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
MyExcel.Cells[2, i + 1] = dt.Columns[i].ColumnName;//生成表头
}
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Bold = true;
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Font.Size = 12;
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置对齐方式
MyExcel.get_Range(MyExcel.Cells[2, 1], MyExcel.Cells[2, dt.Columns.Count]).Borders.LineStyle = "1";
}
int Rows = 2;
for (int i = 0; i < dt.Rows.Count; i++)
{
Rows += 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
MyExcel.Cells[Rows, j + 1] = "" + dt.Rows[i][j].ToString();//导入数据
}
MyExcel.get_Range(MyExcel.Cells[Rows, 1], MyExcel.Cells[Rows, dt.Columns.Count]).Borders.LineStyle = "1";
}
sheet.Columns.AutoFit();//自动适应宽度
MyExcel.Visible = true;
}
catch (Exception) { }
finally { }
}