/// <summary>
/// DataTable 数据至二进制
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="header"></param>
/// <param name="commnet"></param>
/// <returns>二进制数组</returns>
public byte[] Export(DataTable dt)
{
_workBook = new XSSFWorkbook();
ISheet sheet = _workBook.CreateSheet();
if (header == "钢联数据")
{
_workBook.SetSheetName(0, "Sheet1");
}
else
{
_workBook.SetSheetName(0, "数据");
}
//设置列宽
setColumnWidth(dt, sheet);
int rowIndex = 0;
foreach (DataRow dr in dt.Rows)
{
//新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = _workBook.CreateSheet();
}
if (!string.IsNullOrEmpty(header))
{
IRow titleRow = sheet.CreateRow(0);
createTitleComment(sheet, titleRow);
rowIndex = 1;
}
}
//设置单元格数据
setCellsValue(sheet, dt, dr, rowIndex);
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
_workBook.Write(ms);
ms.Flush();
_workBook.Clear();
_workBook = null;
return(ms.ToArray());
}
}
private void groupDataFileButton_Click(object sender, EventArgs e)
{
string filename = FileHelper.OpenFileDialog("请选择简报数据文件", "xlsx文件|*.xlsx");
if (filename != "")
{
this.groupDataFileText.Text = filename;
}
else
{
MsgBox.ShowError("请选择有效文件！");
return;
}
groupCombo.Items.Clear(); //清空Combo中的所有项
//开始读取Sheet名称
XSSFWorkbook workbook;
try
{
using (FileStream stream = File.OpenRead(filename))
{
workbook = new XSSFWorkbook(stream);
}
}
catch (Exception ex)
{
MsgBox.ShowError("错误: " + ex.Message);
this.groupDataFileText.Text = "";
return;
}
int SheetCount = workbook.NumberOfSheets; //获取表的数量
for (int i = 0; i < SheetCount; i++) //逐个加入Combo
{
groupCombo.Items.Add(workbook.GetSheetName(i));
}
workbook.Clear(); //清空workbook，释放内存。
}
/// <summary>
/// 导出文件
/// </summary>
/// <param name="list">数据集合List</param>
/// <param name="head">列集合</param>
/// <param name="title">导出文件名称</param>
/// <param name="rootpath">导出路径</param>
/// <returns></returns>
public static string ExportListToExcelNew(IQueryable list, List <ExportObj> head, string title, string rootpath, string filename)
{
try
{
string FileName = filename.ToString() + "_" + title + ".xlsx";
string strPath = Path.Combine(rootpath, FileName);
if (Directory.Exists(rootpath) == false)
{
Directory.CreateDirectory(rootpath);
}
//文件流对象
using (FileStream filestream = new FileStream(strPath, FileMode.Create, FileAccess.Write))
{
Type type = list.ElementType;
PropertyInfo[] properties = type.GetProperties();
Int32 i = 0;
Int32 j = 0;
//打开Excel对象
XSSFWorkbook workbook = new XSSFWorkbook();
IDataFormat format = workbook.CreateDataFormat();
//set int format
ICellStyle cellStyleInt = workbook.CreateCellStyle();
cellStyleInt.DataFormat = format.GetFormat("#,##0");
cellStyleInt.WrapText = true;
//set decimal format
ICellStyle cellStyleDecimal = workbook.CreateCellStyle();
cellStyleDecimal.DataFormat = format.GetFormat("#,##0.0000");
cellStyleDecimal.WrapText = true;
//set float format
ICellStyle cellStylefloat = workbook.CreateCellStyle();
cellStylefloat.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
cellStylefloat.WrapText = true;
//set double format
ICellStyle cellStyledouble = workbook.CreateCellStyle();
cellStyledouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyledouble.WrapText = true;
ICellStyle cellStyleWrapText = workbook.CreateCellStyle();
cellStyleWrapText.WrapText = true;
//创建一个字体样式对象
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyleWrapTextTitle = workbook.CreateCellStyle();
cellStyleWrapTextTitle.WrapText = true;
cellStyleWrapTextTitle.VerticalAlignment = VerticalAlignment.Center;
cellStyleWrapTextTitle.SetFont(font);
//Excel的Sheet对象
ISheet sheet = workbook.CreateSheet(title);
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 22 * 256);
//生成sheet第一行列名
IRow headerRow = sheet.CreateRow(0);
foreach (var item in head)
{
if (type.GetProperty(item.key) != null)
{
ICell cell = headerRow.CreateCell(j);
cell.CellStyle = cellStyleWrapTextTitle;
cell.SetCellValue(item.Name);
j++;
}
}
//生成sheet数据部分
j = 1;
foreach (var obj in list)
{
//Writelog(string.Format("【创建行开始】"));
IRow dataRow = sheet.CreateRow(j);
//Writelog(string.Format("【创建行结束】"));
i = 0;
foreach (var item in head)
{
//Writelog(string.Format("【列开始】行数{0},列名{1}", j, item.key));
PropertyInfo column = type.GetProperty(item.key);
if (column != null)
{
ICell cell = dataRow.CreateCell(i);
Type cellType = item.DataType;
if (column.GetValue(obj, null) != null)
{
//整数123,456
if (cellType == typeof(int))
{
cell.SetCellValue((int)column.GetValue(obj, null));
cell.CellStyle = cellStyleInt;
}
//金额123,456
else if (cellType == typeof(decimal))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyleDecimal;
}
else if (cellType == typeof(float))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStylefloat;
}
else if (cellType == typeof(double))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyledouble;
}
else
{
cell.SetCellValue(column.GetValue(obj, null).ToString());
cell.CellStyle.WrapText = true;
}
}
i++;
}
}
j++;
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(filestream);
workbook.Clear();
}
return(rootpath + FileName);
}
catch (Exception ex)
{
return("");
}
}
public void Dispose()
{
xssfWorkbook?.Clear();
}
