导出EXCEL
金蝶云·星空-BOS平台
金蝶云社区-assassinl10
assassinl10
4人赞赏了该文章 2,912次浏览 未经作者许可，禁止转载编辑于2018年01月11日 08:44:40

插件：


[password]123456[/password]
#region 导出EXCEL
public static void ExcelOut(List entryList, IDynamicFormView view, string fileType = "xlsx")
{
IWorkbook workbook = null;
if (fileType.EqualsIgnoreCase("xls"))
{
workbook = new HSSFWorkbook();
}
else if (fileType.EqualsIgnoreCase("xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
view.ShowErrMessage("您输入的excel格式不正确");
return;
}

#region Excel 样式

//通用样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center; //左右居中
style.VerticalAlignment = VerticalAlignment.Center; // 上下居中
style.WrapText = true; // 自动换行
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;

// 设置字体
IFont headfont = workbook.CreateFont();
headfont.IsBold = true;
headfont.FontName = "宋体";
headfont.FontHeightInPoints = (short)12; // 字体大小

// 另一个样式
ICellStyle headstyle = workbook.CreateCellStyle();
headstyle.CloneStyleFrom(style);
headstyle.SetFont(headfont);
headstyle.IsLocked = true;
//headstyle.FillForegroundColor = HSSFColor.Brown.Index; //背景色设置
//headstyle.FillPattern = FillPattern.SolidForeground;//背景色设置--必须方能显示

// 另一个字体样式
IFont columnHeadFont = workbook.CreateFont();
columnHeadFont.FontName = "宋体";
columnHeadFont.FontHeightInPoints = (short)10;

// 列头的样式
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.CloneStyleFrom(style);
columnStyle.SetFont(columnHeadFont);

#endregion

#region 构建导出数据包

foreach (var entityName in entryList)
{
List outFieldKes = new List(); //需要导出的字段
outFieldKes.Clear();
var entity = view.BusinessInfo.GetEntity(entityName);

foreach (Field fld in entity.Fields)
{
FieldAppearance fieldAp = view.LayoutInfo.GetFieldAppearance(fld.Key);

if (view.StyleManager.GetVisible(fieldAp) && fld.IsExportFieldTemplateControl() &&
fieldAp.IsVisible(view.OpenParameter.Status))
{
outFieldKes.Add(fld); //构建需要导出的字段
}
}

DynamicObjectCollection curRows = null;
if (view.Model.GetEntityDataObject(entity) == null)
{
curRows = new DynamicObjectCollection(view.Model.DataObject.DynamicObjectType);
curRows.Add(view.Model.DataObject);
}
else
{
curRows = view.Model.GetEntityDataObject(entity);
}

#region 创建工作表

ISheet sheet = workbook.CreateSheet(view.BusinessInfo.GetElement(entityName).Name);
IRow rowHeader = sheet.CreateRow(0); //创建表头行
for (int i = 0; i < outFieldKes.Count(); i++)
{
ICell cell = rowHeader.CreateCell(i);
cell.SetCellValue(outFieldKes[i].Name.ToString());
cell.CellStyle = headstyle;
sheet.SetColumnWidth(i, 256*15);
}
int rowline = 0;
if (!curRows.IsNullOrEmpty())
{
foreach (var item in curRows)
{
rowline++;
IRow row = sheet.CreateRow(rowline);
int j = 0;
foreach (var field in outFieldKes)
{
ICell cell = row.CreateCell(j);
columnStyle.WrapText = true;
cell.CellStyle = columnStyle;

#region 根据字段类型进行赋值

if (field is BaseDataField)
{
string baseName = (item[field.Key] as DynamicObject) == null
? ""
: (item[field.Key] as DynamicObject)[
(field as BaseDataField).NameProperty.PropertyName] == null
? ""
: (item[field.Key] as DynamicObject)[
(field as BaseDataField).NameProperty.PropertyName].ToString().Trim();
cell.SetCellValue(baseName);
}
else if (field is BasePropertyField)
{
string displayfield =
(view.LayoutInfo.GetFieldAppearance(field.Key) as BasePropertyFieldAppearance)
.SourceFieldAppearance.Field.PropertyName;
string value = (item[field.ControlField.Key + "_Ref"] as DynamicObject) == null
? ""
: (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield] == null
? ""
: (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield].GetType
()
.Name.ToString()
.EqualsIgnoreCase("String")
? (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield]
.ToString()
.Trim()
: ((item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield] as
DynamicObject)["Name"].ToString().Trim();
cell.SetCellValue(value);
}
else if (field is PictureField || field is ImageFileServerField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
#region 填充图片

byte[] bytes = GetImageBytes(values, view);
if (bytes != null)
{
row.Height = 80*20;
sheet.SetColumnWidth(j, 256*20);
columnStyle.WrapText = false;
cell.CellStyle = columnStyle;
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();

if (fileType.EqualsIgnoreCase("xls"))
{
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, j, rowline,
j + 1,
rowline + 1);
HSSFPicture pict = (HSSFPicture) patriarch.CreatePicture(anchor, pictureIdx);
}
else if (fileType.EqualsIgnoreCase("xlsx"))
{
XSSFClientAnchor anchor = new XSSFClientAnchor(1*10000, 1*10000, 0, 0, j,
rowline, j + 1,
rowline + 1);
XSSFPicture pict = (XSSFPicture) patriarch.CreatePicture(anchor, pictureIdx);
}

}

#endregion
}
}
else if (field is BillStatusField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
var enumItemName = "";
BillStatusField billStatusField = field as BillStatusField;
var enumObj = billStatusField.StatusItems;
var enumItems = enumObj.FirstOrDefault(p => p.StatusValue.Equals(values));
enumItemName = enumItems == null ? values : enumItems.StatusName.ToString();
cell.SetCellValue(enumItemName);
}
}
else if (field is ComboField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
var enumItemName = "";

ComboField comboField = field as ComboField;
var enumObj = (EnumObject) comboField.EnumObject;
//根据枚举值获取枚举项，然后拿枚举项的枚举名称
var enumItems = enumObj.Items.FirstOrDefault(p => p.Value.Equals(values));
enumItemName = enumItems == null ? values : enumItems.Caption.ToString();
cell.SetCellValue(enumItemName);
}
}
else if (field is DateTimeField || field is TimeField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
DateTime dt = Convert.ToDateTime(values);
string formatStr = view.LayoutInfo.GetFieldAppearance(field.Key).DisplayFormatString;
cell.SetCellValue(dt.ToString(formatStr));
}
}
else if (field is DecimalField)
{
Double dc = Convert.ToDouble(item[field.Key] == null ? "" : item[field.Key].ToString());
if (!dc.Equals(0))
{
cell.SetCellValue(dc);
}
}
else
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
cell.SetCellValue(values);
}
}

#endregion

j++;
}
}
}

#endregion

}

#endregion

#region 生成Excel

string fileName = string.Format("{0}_{1}", view.BusinessInfo.GetForm().Name,
DateTime.Now.ToString("yyyyMMddHHmmssff"));

string[] illegalStrs = new string[] {"/", "\\"};
foreach (var str in illegalStrs)
{
fileName = fileName.Replace(str, "");
}

fileName = PathUtils.GetValidFileName(fileName);
string filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
string outServicePath = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, fileName);
filePath += "." + fileType;
outServicePath += "." + fileType;
using (Stream stream = File.OpenWrite(filePath))
{
workbook.Write(stream);
}

#endregion

#region 弹出下载路径窗口

//下载文件
DynamicFormShowParameter param = new DynamicFormShowParameter();
param.FormId = "BOS_FileDownLoad";
param.OpenStyle.ShowType = ShowType.Modal;
param.CustomParams.Add("IsExportData", "true");
param.CustomParams.Add("url", outServicePath);

view.ShowForm(param);

#endregion

}

public static void ListExcelOut(IListView view, string fileType = "xlsx")
{
IWorkbook workbook = null;
if (fileType.EqualsIgnoreCase("xls"))
{
workbook = new HSSFWorkbook();
}
else if (fileType.EqualsIgnoreCase("xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
view.ShowErrMessage("您输入的excel格式不正确");
return;
}

#region Excel 样式

//通用样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center; //左右居中
style.VerticalAlignment = VerticalAlignment.Center; // 上下居中
style.WrapText = true; // 自动换行
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;

// 设置字体
IFont headfont = workbook.CreateFont();
headfont.IsBold = true;
headfont.FontName = "宋体";
headfont.FontHeightInPoints = (short) 12; // 字体大小

// 另一个样式
ICellStyle headstyle = workbook.CreateCellStyle();
headstyle.CloneStyleFrom(style);
headstyle.SetFont(headfont);
headstyle.IsLocked = true;
//headstyle.FillForegroundColor = HSSFColor.Brown.Index; //背景色设置
//headstyle.FillPattern = FillPattern.SolidForeground;//背景色设置--必须方能显示

// 另一个字体样式
IFont columnHeadFont = workbook.CreateFont();
columnHeadFont.FontName = "宋体";
columnHeadFont.FontHeightInPoints = (short) 10;

// 列头的样式
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.CloneStyleFrom(style);
columnStyle.SetFont(columnHeadFont);

#endregion

#region 构建导出数据包

List outFieldKeys = new List(); //需要导出的字段
outFieldKeys.Clear();
ListHeader header = ((IListModel) view.Model).Header;
if (header != null && header.GetChildCount() > 0)
{
for (int i = 0; i < header.GetChilds().Count; i++)
{
ListHeader header2 = header.GetChilds()[i];
if (header2.Visible)
{
Field fld = view.BillBusinessInfo.GetField(header2.RealKey);
outFieldKeys.Add(fld);
}
}
}

#endregion

#region 构建EXCEL

ISheet sheet = workbook.CreateSheet(view.BillLayoutInfo.Caption);
IRow rowHeader = sheet.CreateRow(0); //创建表头行

for (int i = 0; i < outFieldKeys.Count; i++)
{
ICell cell = rowHeader.CreateCell(i);
cell.SetCellValue(outFieldKeys[i].Name.ToString());
cell.CellStyle = headstyle;
sheet.SetColumnWidth(i, 256*15);

}

for (var rowline = 0; rowline < view.SelectedRowsInfo.Count; rowline++)
{
IRow rowBody = sheet.CreateRow(rowline + 1); //创建表体行
var item = view.SelectedRowsInfo[rowline].DataRow;

for (int i = 0; i < outFieldKeys.Count; i++)
{
ICell cell = rowBody.CreateCell(i);
columnStyle.WrapText = true;
cell.CellStyle = columnStyle;
Field field = outFieldKeys[i];

#region 根据字段类型进行赋值

if (field is BaseDataField)
{
string baseName = (item[field.Key + "_Ref"] as DynamicObject) == null
? ""
: (item[field.Key + "_Ref"] as DynamicObject)[
(field as BaseDataField).NameProperty.PropertyName] == null
? ""
: (item[field.Key + "_Ref"] as DynamicObject)[
(field as BaseDataField).NameProperty.PropertyName].ToString().Trim();
cell.SetCellValue(baseName);
}
else if (field is BasePropertyField)
{
string displayfield =
(view.BillLayoutInfo.GetFieldAppearance(field.Key) as BasePropertyFieldAppearance)
.SourceFieldAppearance.Field.PropertyName;

string value = (item[field.ControlField.Key + "_Ref"] as DynamicObject) == null
? ""
: (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield] == null
? ""
: (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield].GetType()
.Name.ToString()
.EqualsIgnoreCase("String")
? (item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield].ToString()
.Trim()
: ((item[field.ControlField.Key + "_Ref"] as DynamicObject)[displayfield] as
DynamicObject)["Name"].ToString().Trim();
cell.SetCellValue(value);
}
else if (field is PictureField || field is ImageFileServerField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
#region 填充图片

byte[] bytes = GetImageBytes(item[field.Key], view);
if (bytes != null)
{
rowBody.Height = 80*20;
sheet.SetColumnWidth(i, 256*20);
columnStyle.WrapText = false;
cell.CellStyle = columnStyle;
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();

if (fileType.EqualsIgnoreCase("xls"))
{
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 0, i, rowline + 1,
i + 1,
rowline + 2);
HSSFPicture pict = (HSSFPicture) patriarch.CreatePicture(anchor, pictureIdx);
}
else if (fileType.EqualsIgnoreCase("xlsx"))
{
XSSFClientAnchor anchor = new XSSFClientAnchor(1*10000, 1*10000, 0, 0, i,
rowline + 1, i + 1,
rowline + 2);
XSSFPicture pict = (XSSFPicture) patriarch.CreatePicture(anchor, pictureIdx);
}

}

#endregion
}
}
else if (field is BillStatusField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
var enumItemName = "";
BillStatusField billStatusField = field as BillStatusField;
var enumObj = billStatusField.StatusItems;
//根据枚举值获取枚举项，然后拿枚举项的枚举名称
var enumItems = enumObj.FirstOrDefault(p => p.StatusValue.Equals(values));
enumItemName = enumItems == null ? values : enumItems.StatusName.ToString();
cell.SetCellValue(enumItemName);
}
}
else if (field is ComboField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
var enumItemName = "";
ComboField comboField = field as ComboField;
var enumObj = (EnumObject) comboField.EnumObject;
//根据枚举值获取枚举项，然后拿枚举项的枚举名称
var enumItems = enumObj.Items.FirstOrDefault(p => p.Value.Equals(values));
enumItemName = enumItems == null ? values : enumItems.Caption.ToString();
cell.SetCellValue(enumItemName);
}
}
else if (field is DateTimeField || field is TimeField)
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
DateTime dt = Convert.ToDateTime(values);
cell.SetCellValue(dt.ToString());
}
}
else if (field is DecimalField)
{
string values = item[field.Key] == null ? "0" : item[field.Key].ToString();
Double dc = Convert.ToDouble(values.Trim());
if (!dc.Equals(0))
{
cell.SetCellValue(dc);
}
}
else
{
string values = item[field.Key] == null ? "" : item[field.Key].ToString();
if (!String.IsNullOrEmpty(values.Trim()))
{
cell.SetCellValue(values);
}
}

#endregion

}
}

#endregion

#region 生成Excel

string fileName = string.Format("{0}_{1}", view.BusinessInfo.GetForm().Name,
DateTime.Now.ToString("yyyyMMddHHmmssff"));

string[] illegalStrs = new string[] {"/", "\\"};
foreach (var str in illegalStrs)
{
fileName = fileName.Replace(str, "");
}

fileName = PathUtils.GetValidFileName(fileName);
string filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
string outServicePath = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, fileName);
filePath += "." + fileType;
outServicePath += "." + fileType;
using (Stream stream = File.OpenWrite(filePath))
{
workbook.Write(stream);
}

#endregion

#region 弹出下载路径窗口

//下载文件
DynamicFormShowParameter param = new DynamicFormShowParameter();
param.FormId = "BOS_FileDownLoad";
param.OpenStyle.ShowType = ShowType.Modal;
param.CustomParams.Add("IsExportData", "true");
param.CustomParams.Add("url", outServicePath);

view.ShowForm(param);

#endregion
}

#endregion

推荐阅读

EXCEL导出支持

这几天参加K/3 Cloud培训，发现无论打印输出还是报表输出都不支持EXCEL导出，能否在后续的开发中增加该功能以增强系统的易用性？ 你好，我们有“引出”功能 …

    文章

    1,358

案例七：导出Excel

案例列表上导出Excel。 参考链接：[链接]https://vip.kingdee.com/article/68408773014262272 [链接]htt …

    知识

    2,969

单据体如何导出excel?

[图片] 像我单据体里有比较多的16年、17年、18年动态列标题，列的标题是插件动态指定的。 我需要把某张单据导出excel，如果做成打印模板，这样我不能动态指 …

    文章

    1,371