// 由微信类生成Excel
private static void ObjectExport(ExportExcelPageMaker configData,
Dictionary<string, ICellStyle> contentStyles, ISheet sheet, ObjectListModel objectLM)
{
int rowIndex = 1;
foreach (ObjectContainer container in objectLM.List)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
object receiver = container.MainObject;
string strValue = string.Empty;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = receiver.MemberValue(fieldInfo.NickName).ConvertToString();
CellPadding(strValue, cell, fieldInfo);
}
else
strValue = container.Decoder.GetNameString(fieldInfo.NickName);
cell.CellStyle = contentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}
// 由微信类生成Excel
private static void ObjectExport(ExportExcelPageMaker configData,
Dictionary <string, ICellStyle> contentStyles, ISheet sheet, ObjectListModel objectLM)
{
int rowIndex = 1;
foreach (ObjectContainer container in objectLM.List)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
object receiver = container.MainObject;
string strValue = string.Empty;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = receiver.MemberValue(fieldInfo.NickName).ConvertToString();
CellPadding(strValue, cell, fieldInfo);
}
else
{
strValue = container.Decoder.GetNameString(fieldInfo.NickName);
}
cell.CellStyle = contentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}
// 模板
public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
ExportExcelPageMaker headerConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = true
};
ExportExcelPageMaker contentConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
{
UserBorder = false
};
foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
{
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
contentConfigData, fieldInfo, NPOIWrite.Model.Content);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
headerConfigData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
//string strFileName = @"D:\EmportTemplateTest.xls";
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
//{
// workbook.Write(fs);
//}
workbook.Write(ms);
ms.Flush();
return(ms.ToArray());
}
}
// 表格头部设置
private static void HeaderSetting(ExportExcelPageMaker configData, IWorkbook workbook, ISheet sheet)
{
IRow dataRow = sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
int colWith = GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
index++;
}
}
// 表格头部设置
private static void HeaderSetting(ExportExcelPageMaker configData, IWorkbook workbook, ISheet sheet)
{
IRow dataRow = sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
int colWith = GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
index++;
}
}
public static byte[] ExportExcel(OutputData dataSource, ExportExcelPageMaker configData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(configData.fMetaData.Table.TableDesc);
HeaderSetting(configData, workbook, sheet);
Dictionary <string, ICellStyle> ContentStyles = GetContentStyles(workbook, configData);
if (dataSource.Data is DataSet)
{
DataSet ds = (DataSet)dataSource.Data;
TkDebug.AssertArgumentNull(ds, "ds", null);
DataTable dt = ds.Tables[configData.fMetaData.Table.TableName];
if (dt != null)
{
DataTableExport(configData, ContentStyles, sheet, dt);
}
}
if (dataSource.Data is ObjectListModel)
{
ObjectListModel objectLM = (ObjectListModel)dataSource.Data;
if (objectLM.List != null)
{
ObjectExport(configData, ContentStyles, sheet, objectLM);
}
}
workbook.Write(ms);
ms.Flush();
return(ms.ToArray());
}
}
public static byte[] ExportExcel(OutputData dataSource, ExportExcelPageMaker configData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(configData.fMetaData.Table.TableDesc);
HeaderSetting(configData, workbook, sheet);
Dictionary<string, ICellStyle> ContentStyles = GetContentStyles(workbook, configData);
if (dataSource.Data is DataSet)
{
DataSet ds = (DataSet)dataSource.Data;
TkDebug.AssertArgumentNull(ds, "ds", null);
DataTable dt = ds.Tables[configData.fMetaData.Table.TableName];
if (dt != null)
{
DataTableExport(configData, ContentStyles, sheet, dt);
}
}
if (dataSource.Data is ObjectListModel)
{
ObjectListModel objectLM = (ObjectListModel)dataSource.Data;
if (objectLM.List != null)
{
ObjectExport(configData, ContentStyles, sheet, objectLM);
}
}
workbook.Write(ms);
ms.Flush();
return ms.ToArray();
}
}
internal static ICellStyle BorderAndFontSetting(IWorkbook workbook, ExportExcelPageMaker configData,
Tk5FieldInfoEx metaInfo, Model model)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
if (configData.UserBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
if (model == Model.Content)
{
IFont fontContent = FontSetting(workbook, configData.Content);
cellStyle.SetFont(fontContent);
if (metaInfo.Extension != null)
{
AlignSetting(metaInfo.Extension.Align, cellStyle);
}
else
{
AlignSetting(configData.Content.Align, cellStyle);
}
}
else
{
AlignSetting(configData.Header.Align, cellStyle);
IFont fontHeader = FontSetting(workbook, configData.Header);
cellStyle.SetFont(fontHeader);
}
return(cellStyle);
}
//生成Dictionary<NickName, ICellStyle>， 每一列对应每一个ICellStyle，通过NickName来获取ICellStyle
private static Dictionary <string, ICellStyle> GetContentStyles(IWorkbook workbook, ExportExcelPageMaker configData)
{
Dictionary <string, ICellStyle> NameToStyle = new Dictionary <string, ICellStyle>();
IDataFormat format = workbook.CreateDataFormat();
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICellStyle cellStyle = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Content);
if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format))
{
cellStyle.DataFormat = format.GetFormat("@");
}
else
{
switch (fieldInfo.DataType)
{
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
if (fieldInfo.DataType == TkDataType.Money)
{
cellStyle.DataFormat = format.GetFormat("￥#,##0");
}
else
{
cellStyle.DataFormat = format.GetFormat("0");
}
break;
case TkDataType.String:
case TkDataType.Text:
case TkDataType.Guid:
case TkDataType.Xml:
cellStyle.DataFormat = format.GetFormat("@");
break;
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
{
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
}
break;
}
}
NameToStyle.Add(fieldInfo.NickName, cellStyle);
}
return(NameToStyle);
}
// 有DataTable生成Excel
private static void DataTableExport(ExportExcelPageMaker configData,
Dictionary <string, ICellStyle> contentStyles, ISheet sheet, DataTable dataTable)
{
int rowIndex = 1;
foreach (DataRow row in dataTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
string strValue = string.Empty;
Tk5ExtensionConfig ex = fieldInfo.Extension;
SimpleFieldControl sfctrl = fieldInfo.InternalControl;
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = (row[fieldInfo.NickName]).ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null && sfctrl.SrcControl == ControlType.CheckBox)
{
if ((ex != null && strValue == ex.CheckValue) ||
(ex == null && strValue == "1"))
{
cell.SetCellValue("√");
}
}
else
{
CellPadding(strValue, cell, fieldInfo);
}
}
}
else
{
strValue = row[fieldInfo.NickName + "_Name"].ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null &&
(sfctrl.SrcControl == ControlType.CheckBoxList ||
sfctrl.SrcControl == ControlType.MultipleEasySearch))
{
MultipleDecoderData data = MultipleDecoderData.ReadFromString(strValue);
cell.SetCellValue(string.Join(", ", data));
}
else
{
cell.SetCellValue(strValue);
}
}
}
cell.CellStyle = contentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}
// 导出模板
public static byte[] CreateExcelTemplate(ExportExcelPageMaker configData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(configData.fMetaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook, configData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
workbook.Write(ms);
ms.Flush();
return ms.ToArray();
}
}
//生成Dictionary<NickName, ICellStyle>， 每一列对应每一个ICellStyle，通过NickName来获取ICellStyle
private static Dictionary<string, ICellStyle> GetContentStyles(IWorkbook workbook, ExportExcelPageMaker configData)
{
Dictionary<string, ICellStyle> NameToStyle = new Dictionary<string, ICellStyle>();
IDataFormat format = workbook.CreateDataFormat();
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICellStyle cellStyle = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Content);
if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format))
cellStyle.DataFormat = format.GetFormat("@");
else
{
switch (fieldInfo.DataType)
{
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
if (fieldInfo.DataType == TkDataType.Money)
cellStyle.DataFormat = format.GetFormat("￥#,##0");
else
cellStyle.DataFormat = format.GetFormat("0");
break;
case TkDataType.String:
case TkDataType.Text:
case TkDataType.Guid:
case TkDataType.Xml:
cellStyle.DataFormat = format.GetFormat("@");
break;
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
else
cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
break;
}
}
NameToStyle.Add(fieldInfo.NickName, cellStyle);
}
return NameToStyle;
}
internal static ICellStyle BorderAndFontSetting(IWorkbook workbook, ExportExcelPageMaker configData,
Tk5FieldInfoEx metaInfo, Model model)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
if (configData.UserBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
if (model == Model.Content)
{
IFont fontContent = FontSetting(workbook, configData.Content);
cellStyle.SetFont(fontContent);
if (metaInfo.Extension != null)
{
AlignSetting(metaInfo.Extension.Align, cellStyle);
}
else
{
AlignSetting(configData.Content.Align, cellStyle);
}
}
else
{
AlignSetting(configData.Header.Align, cellStyle);
IFont fontHeader = FontSetting(workbook, configData.Header);
cellStyle.SetFont(fontHeader);
}
return cellStyle;
}
// 有DataTable生成Excel
private static void DataTableExport(ExportExcelPageMaker configData,
Dictionary<string, ICellStyle> contentStyles, ISheet sheet, DataTable dataTable)
{
int rowIndex = 1;
foreach (DataRow row in dataTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
string strValue = string.Empty;
Tk5ExtensionConfig ex = fieldInfo.Extension;
SimpleFieldControl sfctrl = fieldInfo.InternalControl;
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = (row[fieldInfo.NickName]).ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null && sfctrl.SrcControl == ControlType.CheckBox)
{
if ((ex != null && strValue == ex.CheckValue) ||
(ex == null && strValue == "1"))
cell.SetCellValue("√");
}
else
CellPadding(strValue, cell, fieldInfo);
}
}
else
{
strValue = row[fieldInfo.NickName + "_Name"].ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (sfctrl != null &&
(sfctrl.SrcControl == ControlType.CheckBoxList ||
sfctrl.SrcControl == ControlType.MultipleEasySearch))
{
MultipleDecoderData data = MultipleDecoderData.ReadFromString(strValue);
cell.SetCellValue(string.Join(", ", data));
}
else
cell.SetCellValue(strValue);
}
}
cell.CellStyle = contentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}
// 模板
public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
{
MemoryStream ms = new MemoryStream();
using (ms)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
int index = 0;
ExportExcelPageMaker headerConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = true };
ExportExcelPageMaker contentConfigData =
new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = false };
foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
{
int colWith = NPOIWrite.GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
contentConfigData, fieldInfo, NPOIWrite.Model.Content);
HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
sheet.SetDefaultColumnStyle(index, styleContent);
if (dataValidate != null)
{
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
headerConfigData, fieldInfo, NPOIWrite.Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
index++;
}
//string strFileName = @"D:\EmportTemplateTest.xls";
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
//{
// workbook.Write(fs);
//}
workbook.Write(ms);
ms.Flush();
return ms.ToArray();
}
}