protected override List <ColumnDefine> FetchTablesColumns()
{
using var conn = CreateDbConnection();
var primaryKeys = conn.Query($@"
SELECT column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name = '{Options.SourceTable}'
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;"
).ToList();
var columns = conn
.Query(
$"SELECT * FROM [{Options.SourceDatabase}].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{Options.SourceTable}';")
.ToList();
var columnDefines = new List <ColumnDefine>();
foreach (IDictionary <string, dynamic> column in columns)
{
var columnDefine = new ColumnDefine
{
DataType = ConvertToClickHouseDataType(column["DATA_TYPE"]),
IsPrimary = primaryKeys.Select(f => f.PRIMARYKEYCOLUMN).ToList()
.Contains(column["COLUMN_NAME"]),
Name = column["COLUMN_NAME"]
};
columnDefines.Add(columnDefine);
}
return(columnDefines);
}
protected override List <ColumnDefine> FetchTablesColumns()
{
using var conn = CreateDbConnection();
var columns = conn.Query($"show columns from `{Options.SourceDatabase}`.`{Options.SourceTable}`;")
.ToList();
var columnDefines = new List <ColumnDefine>();
foreach (IDictionary <string, dynamic> column in columns)
{
var columnDefine = new ColumnDefine
{
DataType = ConvertToClickHouseDataType(column["Type"]),
IsPrimary = column["Key"] == "PRI",
Name = column["Field"]
};
columnDefines.Add(columnDefine);
}
return(columnDefines);
}
public static HSSFWorkbook NoTemplateExport(ColumnDefineCollection columnDefines, DataTable table)
{
//创建工作簿
HSSFWorkbook book = new HSSFWorkbook();
//增加标题Style
HSSFCellStyle styleHeader = (HSSFCellStyle)book.CreateCellStyle();
styleHeader.FillForegroundColor = HSSFColor.BLUE.index;
styleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND;
//设置Font
HSSFFont fontHeader = (HSSFFont)book.CreateFont();
fontHeader.FontName = "Tahoma";
fontHeader.FontHeight = 200;
fontHeader.Color = HSSFColor.WHITE.index;
styleHeader.SetFont(fontHeader);
//设置缺省Style
HSSFCellStyle styleDefault = (HSSFCellStyle)book.CreateCellStyle();
HSSFFont fontDefault = (HSSFFont)book.CreateFont();
styleDefault.SetFont(fontDefault);
fontDefault.FontName = "Tahoma";
fontDefault.FontHeight = 200;
//创建Sheet
HSSFRow row;
HSSFCell cell;
HSSFSheet sheet = (HSSFSheet)book.CreateSheet("Sheet1");
//创建标题列
row = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
style.CloneStyleFrom(styleHeader);
style.Alignment = columnDefine.Align;
cell.CellStyle = style;
//设置值
cell.SetCellValue(columnDefine.Text);
sheet.SetColumnWidth(i, YZExcelHelper.PixelToExcel(columnDefine.Width));
}
foreach (DataRow dataRow in table.Rows)
{
row = (HSSFRow)sheet.CreateRow(sheet.LastRowNum + 1);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
object objValue = dataRow[columnDefine.ColumnName];
//SQL Server数据库中monery4位小数点处理
if (objValue is decimal)
{
objValue = (decimal)Decimal.ToDouble((decimal)objValue);
}
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
if (columnDefine.Style == null)
{
columnDefine.Style = (HSSFCellStyle)book.CreateCellStyle();
columnDefine.Style.CloneStyleFrom(styleDefault);
columnDefine.Style.Alignment = columnDefine.Align;
}
cell.CellStyle = columnDefine.Style;
//设置值
TypeCode typeCode = Type.GetTypeCode(objValue == null ? typeof(string) : objValue.GetType());
switch (typeCode)
{
case TypeCode.Boolean:
cell.SetCellValue(Convert.ToBoolean(objValue));
break;
case TypeCode.DateTime:
DateTime date = (DateTime)objValue;
string strValue = date == DateTime.MinValue ? "" : YZStringHelper.DateToStringL(date);
cell.SetCellValue(strValue);
break;
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.SByte:
case TypeCode.Byte:
cell.SetCellValue(Convert.ToDouble(objValue));
break;
default:
cell.SetCellValue(Convert.ToString(objValue));
break;
}
}
}
return(book);
}
