C# 导出excel按指定模板

来源：互联网发布：淘宝卖家态度恶劣编辑：程序博客网时间：2024/05/22 10:34

private bool ExportForExecl(object sender, DoWorkEventArgs e)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //生成一个workbook对象
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得第一个 sheet
Object Nothing = System.Reflection.Missing.Value;

//公共属性
int rowHeight = 15;
int columnWidth = 2;
int fontSize = 12;
Color color = Color.FromArgb(218, 150, 148);
int beginIndex = 3;//开始行
try
{
foreach (System.Data.DataTable dt in dts)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Range range = null;// 创建一个空的单元格对象
DataRow dr = dt.Rows[i];
string tableName = dr["表名"].ToString();
string tableMemo = dr["表说明"].ToString();
string sheetName = tableMemo.IsNotNullAndEmpty() ? tableMemo : tableName;
if (sheetName.IsNotNullAndEmpty())
{
//sheet名是否有重复，有重复就在名字后面加序数
if (sheetNames.Keys.Contains(sheetName))
{
int m = sheetNames[sheetName] + 1;
sheetName = sheetName + m.ToString();
sheetNames.Add(sheetName, m);
}
else
{
sheetNames.Add(sheetName, 1);
}
//第一个sheet不需要追加
if (i != 0)
{
sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Nothing, sheet, Nothing, Nothing);
}
beginIndex = 3;
sheet.Name = sheetName;
#region 首行
#region 第一行
//公共属性
range = sheet.get_Range("C" + beginIndex, "BI" + beginIndex);// 获取多个单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
range.Borders.LineStyle = 1; // 设置单元格边框
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;// 设置单元格垂直居中
range.RowHeight = rowHeight; // 设置行高
range.ColumnWidth = columnWidth; // 设置列宽
range.Cells.Borders.LineStyle = 1;//设置全边框
range.Font.Size = fontSize; // 设置字体大小

//表名列
range = sheet.get_Range("C" + beginIndex, "F" + beginIndex);// 获取多个单元格
range.Merge(Missing.Value); // 合并单元格
range.Value2 = "表名";
range.Interior.Color = color; // 设置单元格背景色
#endregion

#region 第二行
beginIndex++;
//公共属性
range = sheet.get_Range("C" + beginIndex, "BI" + beginIndex);// 获取多个单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
range.Borders.LineStyle = 1; // 设置单元格边框
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;// 设置单元格垂直居中
range.RowHeight = rowHeight; // 设置行高
range.ColumnWidth = columnWidth; // 设置列宽
range.Cells.Borders.LineStyle = 1;//设置全边框
range.Font.Size = fontSize; // 设置字体大小
range.Interior.Color = color; // 设置单元格背景色

//NO
range = sheet.get_Range("C" + beginIndex, "D" + beginIndex);// 获取多个单元格
range.Merge(Missing.Value); // 合并单元格
range.Value2 = "NO";

//列名
range = sheet.get_Range("E" + beginIndex, "N" + beginIndex);
range.Merge(Missing.Value);
range.Value2 = "列名";

//数据类型
range = sheet.get_Range("O" + beginIndex, "U" + beginIndex);
range.Merge(Missing.Value);
range.Value2 = "数据类型";
#endregion
#endregion
}

#region 数据行
beginIndex++;
//公共属性
range = sheet.get_Range("C" + beginIndex, "BI" + beginIndex);// 获取多个单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
range.Borders.LineStyle = 1; // 设置单元格边框
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.VerticalAlignment = XlVAlign.xlVAlignCenter;// 设置单元格垂直居中
range.RowHeight = rowHeight; // 设置行高
range.ColumnWidth = columnWidth; // 设置列宽
range.Cells.Borders.LineStyle = 1;//设置全边框
range.Font.Size = fontSize; // 设置字体大小

//NO
range = sheet.get_Range("C" + beginIndex, "D" + beginIndex);// 获取多个单元格
range.Merge(Missing.Value); // 合并单元格
range.Value2 = dr["字段序号"];

//列名
range = sheet.get_Range("E" + beginIndex, "N" + beginIndex);
range.Merge(Missing.Value);
range.Value2 = dr["字段名"];

#endregion

//sheet.get_Range(sheet.Cells[29, 2], sheet.Cells[29, 2]).Orientation = Microsoft.Office.Interop.Excel.XlOrientation.xlVertical;//设置29行第2个单元格的字体竖直居中在单元格内
//range = sheet.get_Range("A1", Missing.Value);// 获取单个单元格
//range.Font.Bold = true; // 加粗字体
// 如需打印文件
//sheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
excel.ActiveWorkbook.SaveAs(_ToPath, Nothing, Nothing, Nothing, Nothing, Nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
excel.ActiveWorkbook.Close(Nothing, Nothing, Nothing); //关闭Excel Work Book对象
excel.Quit(); //关闭Excel组件对象
GC.Collect();
MessageBox.Show("生成成功！");
System.Diagnostics.Process.Start(_ToPath);//自动打开excel
// this.Close();//关闭程序
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
this.button3.Enabled = true;
return false;
}
return true;
}

0 0