protected Model.Column[] ProcessHeader(Model.ColumnModel colModel)
{
#region 创建标记对象
var colsize = colModel[0].Sum(e => e.ColSpan); // 总列数
var maps = new int[colModel.Count, colsize];
var columnNames = new Model.Column[colsize];
#endregion
for (int i = 0; i < colModel.Count; i++)
{
var colIndex = 0; // 当前列位置
for (int j = 0; j < colModel[i].Length; j++)
{
var column = colModel[i][j];
#region 计算行列位置
column.Y = i; // 实际行号
if (i > 0) // 从第2行开始纠正受rowspan影响的位置
{
for (var t = colIndex; t < colsize; t++)
{
if (maps[i, t] == 0)
{
break;
}
colIndex++;
}
}
column.X = colIndex;
colIndex += column.ColSpan; // 下一列开始位置
#endregion
#region 标记数据绑定列
if (column.Y + 1 + column.RowSpan >= colModel.Count)
{
columnNames[column.X] = column;
}
#endregion
#region 标记被合并的单元格为 1
if (column.RowSpan > 1)
{
for (var r = 0; r < column.RowSpan; r++)
{
for (var c = 0; c < column.ColSpan; c++)
{
if (r > 0 || c > 0)
{
maps[column.Y + r, column.X + c] = 1;
}
}
}
}
#endregion
}
}
return(columnNames);
}
private IWorkbook Export(DataTable data, Model.ColumnModel colModel)
{
colModel.ThrowIfNull("");
var book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet(data.TableName ?? "Sheet1");
#region 生成表头
var dataColumns = base.ProcessHeader(colModel);
CreateHeader(book, sheet, colModel);
#endregion
#region 生成数据行
var rownum = colModel.Count;
var cellstyles = GetColumnStyles(book, dataColumns);
foreach (DataRow row in data.Rows)
{
IRow dataRow = sheet.CreateRow(rownum++);
foreach (var col in dataColumns)
{
var cell = dataRow.CreateCell(col.X);
cell.CellStyle = cellstyles[col.X];
cell.SetCellValue(row[col.Field].ToString());
}
}
#endregion
return(book);
}
private string GenerateKeyCode(List <Model.ColumnModel> columnModelList)
{
Model.ColumnModel keyColumnModel = columnModelList[0];
return(string.Format("{0} {1}",
GetCSharpTypeString(keyColumnModel.type, keyColumnModel.notnull),
FirstLower(CamelCase(keyColumnModel.name))));
}
private void CreateHeader(IWorkbook book, ISheet sheet, Model.ColumnModel colModel)
{
var rownum = 0;
IFont font = book.CreateFont();
font.IsBold = true;
foreach (var cols in colModel)
{
IRow headRow = sheet.CreateRow(rownum++);
foreach (var col in cols)
{
if (col.Width > 0)
{
sheet.SetColumnWidth(col.X, col.Width * 36);
}
var cell = headRow.CreateCell(col.X);
var cellstyle = book.CreateCellStyle();
cell.CellStyle = cellstyle;
cellstyle.SetFont(font);
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
cell.SetCellValue(col.Title);
if (col.ColSpan > 1 || col.RowSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(col.Y, col.Y + col.RowSpan - 1, col.X, col.X + col.ColSpan - 1));
}
}
}
}
private string GenerateGetModelParameterCode(List <Model.ColumnModel> columnModelList)
{
Model.ColumnModel keyColumnModel = columnModelList[0];
StringBuilder result = new StringBuilder();
result.AppendLine("NpgsqlParameter[] parameters = {");
result.AppendLine("\t\t\t\t" + "new NpgsqlParameter(\"@" + keyColumnModel.name + "\", NpgsqlDbType." + GetNpgsqlDbTypeString(keyColumnModel.type) + ")");
result.AppendLine("\t\t\t};");
result.Append("\t\t\t" + "parameters[0].Value = " + FirstLower(CamelCase(keyColumnModel.name)) + ";");
return(result.ToString());
}
private void CreateHeader(StringBuilder table, Model.ColumnModel colModel)
{
foreach (var row in colModel)
{
table.Append("<tr>");
foreach (var col in row)
{
table.AppendFormat("<th{1}{2}{3}>{0}</th>",
col.Title,
col.ColSpan > 1 ? $" colspan={col.ColSpan}" : string.Empty,
col.RowSpan > 1 ? $" rowspan={col.RowSpan}" : string.Empty,
col.Width > 0 ? $" width={col.Width}" : string.Empty
);
}
table.Append("</tr>");
}
}
public List <Model.ColumnModel> GetColumnModelList(string tableName)
{
List <Model.ColumnModel> result = new List <Model.ColumnModel>();
DataTable dt = GetTableColumns(tableName);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
Model.ColumnModel model = new Model.ColumnModel();
model.name = row["name"].ToString();
model.type = row["type"].ToString();
model.notnull = bool.Parse(row["notnull"].ToString());
model.comment = row["comment"].ToString();
result.Add(model);
}
}
return(result);
}
public Stream ToStream(object data, Model.ColumnModel colModel)
{
IWorkbook book = null;
if (data is DataTable)
{
book = Export(data as DataTable, colModel);
}
else if (data is IEnumerable)
{
book = Export(data as DataTable, colModel);
}
else
{
throw new ArgumentException("data");
}
var ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
public void Export(object data, string fileName, Model.ColumnModel colModel)
{
IWorkbook book = null;
if (data is DataTable)
{
book = Export(data as DataTable, colModel);
}
else if (data is IEnumerable)
{
book = Export(data as IEnumerable, colModel);
}
else
{
throw new ArgumentException("data");
}
using (var fs = new FileStream(fileName, FileMode.Create))
{
book.Write(fs);
fs.Flush();
}
}
public void Export(object data, string fileName, Model.ColumnModel colModel)
{
var table = string.Empty;
if (data is DataTable)
{
table = Export(data as DataTable, colModel);
}
else if (data is IEnumerable)
{
table = Export(data as IEnumerable, colModel);
}
else
{
throw new ArgumentException("data");
}
using (var sr = new StreamWriter(fileName, false, Encoding.UTF8))
{
sr.Write(table);
sr.Flush();
}
}
private string Export(IEnumerable data, Model.ColumnModel colModel)
{
colModel.ThrowIfNull("");
var table = new StringBuilder();
table.Append("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></head><body>");
table.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
#region 生成表头
var dataColumns = base.ProcessHeader(colModel);
CreateHeader(table, colModel);
#endregion
#region 生成数据行
PropertyInfo[] pis = null;
foreach (object row in data)
{
if (pis == null)
{
pis = row.GetType().GetProperties();
}
table.Append("<tr>");
foreach (var col in dataColumns)
{
var pi = pis.FirstOrDefault(p => p.Name == col.Field);
if (pi != null)
{
var value = pi.GetValue(row, null);
table.AppendFormat("<td style=\"vnd.ms-excel.numberformat:@;text-align:{1}\">{0}</td>", value, col.Alignment.ToString().ToLower());
}
}
table.Append("</tr>");
}
#endregion
table.Append("</table></body></html>");
return(table.ToString());
}
public Stream ToStream(object data, Model.ColumnModel colModel)
{
var table = string.Empty;
if (data is DataTable)
{
table = Export(data as DataTable, colModel);
}
else if (data is IEnumerable)
{
table = Export(data as IEnumerable, colModel);
}
else
{
throw new ArgumentException("data");
}
var buffer = Encoding.UTF8.GetBytes(table);
var ms = new MemoryStream();
ms.Write(buffer, 0, buffer.Length);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
private IWorkbook Export(IEnumerable data, Model.ColumnModel colModel)
{
colModel.ThrowIfNull("");
var book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet("Sheet1");
#region 生成表头
var dataColumns = base.ProcessHeader(colModel);
CreateHeader(book, sheet, colModel);
#endregion
#region 生成数据行
var rownum = colModel.Count;
var cellstyles = GetColumnStyles(book, dataColumns);
PropertyInfo[] pis = null;
foreach (var row in data)
{
if (pis == null)
{
pis = row.GetType().GetProperties();
}
IRow dataRow = sheet.CreateRow(rownum++);
foreach (var col in dataColumns)
{
var pi = pis.FirstOrDefault(p => p.Name == col.Field);
if (pi != null)
{
var cell = dataRow.CreateCell(col.X);
cell.CellStyle = cellstyles[col.X];
cell.SetCellValue(pi.GetValue(row, null).ToString());
}
}
}
#endregion
return(book);
}
private string Export(DataTable data, Model.ColumnModel colModel)
{
colModel.ThrowIfNull("");
var table = new StringBuilder();
table.Append("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></head><body>");
table.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
#region 生成表头
var dataColumns = base.ProcessHeader(colModel);
CreateHeader(table, colModel);
#endregion
#region 生成数据行
foreach (DataRow row in data.Rows)
{
table.Append("<tr>");
foreach (var col in dataColumns)
{
if (col.Alignment == Model.HorizontalAlignment.General)
{
table.AppendFormat("<td>{0}</td>", row[col.Field]);
}
else
{
table.AppendFormat("<td style=\"text-align:{1}\">{0}</td>",
row[col.Field], col.Alignment.ToString().ToLower());
}
}
table.Append("</tr>");
}
#endregion
table.Append("</table></body></html>");
return(table.ToString());
}
private string GenerateKeyWhereCode(List <Model.ColumnModel> columnModelList)
{
Model.ColumnModel keyColumnModel = columnModelList[0];
return(string.Format(" {0}=@{0}", keyColumnModel.name));
}
private string GenerateKeyParamCode(List <Model.ColumnModel> columnModelList)
{
Model.ColumnModel keyColumnModel = columnModelList[0];
return(string.Format("{0}", FirstLower(CamelCase(keyColumnModel.name))));
}