c#/.net 导出excel表格

1.本片主要讲述excel导出的样式处理，导出方法看前一篇

static void Main(string[] args)
{
List<ExportModel> list = new List<ExportModel>();
string[] types = new string[4];
types[0] = null;
types[1] = null;
types[3] = null;
string connStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
DataTable dt = conn.GetSchema("Tables", null);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)//循环表
{
bool isTable = dt.Rows[i]["TABLE_TYPE"].ToString() == "BASE TABLE";
//Console.WriteLine(dt.Rows[i]["TABLE_TYPE"]+"");
if (!isTable)
{
continue;
}

//Console.WriteLine(dt.Rows[i]["TABLE_NAME"] + "\t" + dt.Rows[i]["TABLE_CATALOG"] + "\n");//表明，数据库名
#region 列名称
string tableName = dt.Rows[i]["TABLE_NAME"].ToString();
string sql = $"select name from syscolumns where id = object_id('{tableName}')";
types[2] = tableName;
SqlCommand cmd = new SqlCommand(sql, conn);
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
#endregion
#region 列类型
DataTable dt1 = conn.GetSchema(SqlClientMetaDataCollectionNames.Columns, types);
//Console.WriteLine(JsonConvert.SerializeObject(dt1, Formatting.Indented));
#endregion
#region 获取描述
string sql1 = "select a.name as table_name, b.name as column_name, c.value as remarks from sys.tables a left join sys.columns b on a.object_id=b.object_id left join sys.extended_properties c on a.object_id=c.major_id where a.name='" + tableName + "' and c.minor_id<>0 and b.column_id=c.minor_id and a.schema_id=(select schema_id from sys.schemas where name='dbo') ";
SqlCommand cmd1 = new SqlCommand(sql1, conn);
DataSet ds1 = new DataSet();
SqlDataAdapter adapter1 = new SqlDataAdapter(cmd1);
adapter1.Fill(ds1);
//Console.WriteLine(JsonConvert.SerializeObject(ds1, Formatting.Indented));//描述
List<DescripModel> desList = new List<DescripModel>();
for (int j = 0; j < ds1.Tables[0].Rows.Count; j++)
{
DescripModel model = new DescripModel
{
table_name = ds1.Tables[0].Rows[j]["table_name"].ToString(),
column_name = ds1.Tables[0].Rows[j]["column_name"].ToString(),
remarks = ds1.Tables[0].Rows[j]["remarks"].ToString()
};
desList.Add(model);
}

#endregion
ExportModel eModel = new ExportModel { TableName = tableName, BaseName = "Task_BusinessLogic", TableRemark = "表名称" };
eModel.TableInfo = new List<TableModel>();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows[0].Table.Rows.Count; j++)
{
string columnName = ds.Tables[0].Rows[0].Table.Rows[j]["name"].ToString();
string columnType = dt1.Rows[j]["DATA_TYPE"].ToString();
string len = dt1.Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString();

if (string.IsNullOrEmpty(len) == false)
{
int lenInt = int.Parse(len);
if (lenInt == -1)
{
columnType += "(max)";
}
else
{
columnType += "(" + lenInt + ")";
}
}

string description = "";
if (desList.Count > 0)
{
int index = desList.FindIndex(m => m.column_name == columnName);
if (index > 0)
{
description = desList[index].remarks;
}
}

//Console.WriteLine(JsonConvert.SerializeObject(columnName) + JsonConvert.SerializeObject(columnType) + JsonConvert.SerializeObject(description));//列名,类型,描述
TableModel tableModel = new TableModel { ColumnName = columnName, ColumnType = columnType, ColumnDescription = description };
eModel.TableInfo.Add(tableModel);
}
}
list.Add(eModel);
}
}
conn.Close();

string upfilepath = System.AppDomain.CurrentDomain.BaseDirectory;//E:\新建文件夹\ConsoleApp1\ConsoleApp1\bin\Debug\
//Console.WriteLine(upfilepath);
//Console.ReadLine();
if (Directory.Exists(upfilepath) == false)
{
Directory.CreateDirectory(upfilepath);
}
List<string> files = new List<string>();
IWorkbook workbook1 = new HSSFWorkbook();
for (int i = 0; i < list.Count; i++)
{
ISheet sheet1 = workbook1.CreateSheet("数据库表" + i + 1);
//sheet1.SetColumnWidth(1, 180);//设置单元格宽度
sheet1.DefaultColumnWidth = 1 * 16;//单元格默认宽度
sheet1.DefaultRowHeight = 18 * 20;//单元格默认高度
sheet1.SetColumnWidth(2, 45 * 256);//单元格设置宽度
IRow row1 = sheet1.CreateRow(0);//创建行
ICell cell1_0 = row1.CreateCell(0);//创建单元格
cell1_0.SetCellValue("表名：" + list[i].TableName + " 所属数据库：" + list[i].BaseName + " 备注：" + list[i].TableRemark);//单元格插入数据
#region 合并单元格
CellRangeAddress region = new CellRangeAddress(0,0,0,2);
sheet1.AddMergedRegion(region);
#endregion
#region 第一行样式
ICellStyle style = workbook1.CreateCellStyle();//样式
style.VerticalAlignment = VerticalAlignment.Center;//文本垂直居中
HSSFFont font = (HSSFFont)workbook1.CreateFont();//字体样式
font.FontHeightInPoints = 11;//字体大小
font.Color = HSSFColor.White.Index;//字体颜色
style.SetFont(font);
style.FillForegroundColor = HSSFColor.Pink.Index;//68,114,196
HSSFWorkbook wx = (HSSFWorkbook)workbook1;
HSSFPalette palette = wx.GetCustomPalette();
palette.SetColorAtIndex(8, 68, 114, 196);//设置模板颜色
HSSFColor color = palette.FindColor(68, 114, 196);
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = color.Indexed;//设置单元格背景色
style.BorderTop = BorderStyle.Thin;//设置单元格边框粗细
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.TopBorderColor = color.Indexed;//设置单元格边框颜色
style.RightBorderColor = color.Indexed;//设置单元格边框颜色
style.LeftBorderColor = color.Indexed;//设置单元格边框颜色
#region 合并单元格后边框细节处理
for (int k = region.FirstRow; k <= region.LastRow; k++)
{
IRow row = HSSFCellUtil.GetRow(k, (HSSFSheet)sheet1);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
}
#endregion
row1.GetCell(0).CellStyle = style;
#endregion
#region 第二行样式
IRow row2 = sheet1.CreateRow(1);
ICell cell2_0 = row2.CreateCell(0);
cell2_0.SetCellValue("字段名");
ICell cell2_1 = row2.CreateCell(1);
cell2_1.SetCellValue("类型");
ICell cell2_2 = row2.CreateCell(2);
cell2_2.SetCellValue("备注");
ICellStyle style1 = workbook1.CreateCellStyle();//样式
style1.VerticalAlignment = VerticalAlignment.Center;//文本垂直居中
HSSFWorkbook wx1 = (HSSFWorkbook)workbook1;
HSSFPalette palette1 = wx1.GetCustomPalette();
palette1.SetColorAtIndex(9, 217, 226, 243);
HSSFColor color1 = palette1.FindColor(217, 226, 243);
style1.FillPattern = FillPattern.SolidForeground;
style1.FillForegroundColor = color1.Indexed;
style1.BorderTop = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.TopBorderColor = color.Indexed;//设置单元格边框颜色
style1.RightBorderColor = color.Indexed;//设置单元格边框颜色
style1.BottomBorderColor = color.Indexed;//设置单元格边框颜色
style1.LeftBorderColor = color.Indexed;//设置单元格边框颜色
row2.GetCell(0).CellStyle = style1;
row2.GetCell(1).CellStyle = style1;
row2.GetCell(2).CellStyle = style1;
#endregion
#region 列表公用样式
ICellStyle styleComm = workbook1.CreateCellStyle();
styleComm.BorderTop = BorderStyle.Thin;
styleComm.BorderRight = BorderStyle.Thin;
styleComm.BorderBottom = BorderStyle.Thin;
styleComm.BorderLeft = BorderStyle.Thin;
styleComm.TopBorderColor= color.Indexed;
styleComm.RightBorderColor = color.Indexed;
styleComm.BottomBorderColor = color.Indexed;
styleComm.LeftBorderColor = color.Indexed;
#endregion
for (int j = 0; j < list[i].TableInfo.Count; j++)
{
int num = j + 2;
IRow tempRow = sheet1.CreateRow(num);
ICell temp_0 = tempRow.CreateCell(0);
temp_0.SetCellValue(list[i].TableInfo[j].ColumnName);
ICell temp_1 = tempRow.CreateCell(1);
temp_1.SetCellValue(list[i].TableInfo[j].ColumnType);
ICell temp_2 = tempRow.CreateCell(2);
temp_2.SetCellValue(list[i].TableInfo[j].ColumnDescription);
tempRow.GetCell(0).CellStyle = styleComm;
tempRow.GetCell(1).CellStyle = styleComm;
tempRow.GetCell(2).CellStyle = styleComm;
}
}

string guid = Guid.NewGuid().ToString("N");
string file1 = upfilepath + guid + ".xls";
FileStream sw1 = System.IO.File.Create(file1);
workbook1.Write(sw1);
sw1.Close();

Console.WriteLine("结束");
Console.ReadLine();

 

Console.Read();
}

public class TableName
{
public string TABLE_CATALOG { get; set; }

public string TABLE_SCHEMA { get; set; }

public string TABLE_NAME { get; set; }

public string TABLE_TYPE { get; set; }
}

public class DescripModel
{
public string table_name { get; set; }

public string column_name { get; set; }

public string remarks { get; set; }
}

public class ExportModel
{
public string TableName { get; set; }

public string BaseName { get; set; }

public string TableRemark { get; set; }

public List<TableModel> TableInfo { get; set; }
}

public class TableModel
{
public string ColumnName { get; set; }

public string ColumnType { get; set; }

public string ColumnDescription { get; set; }

}