/// <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(); }