/// <summary> /// 导出矿山列表 /// </summary> /// <returns></returns> /// <param name="isAll">是否导出全部数据</param> public static void GetExportList(List <HotelInfo> HotelInfo, string path) { try { var dateTime = DateTime.Now.ToString("yyyy-MM-dd"); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "同程酒店" + dateTime; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.IsAllSizeColumn = true; excelconfig.FileName = path + "同程酒店" + dateTime + ".xlsx"; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Id", ExcelColumn = "序号" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "HotelName", ExcelColumn = "酒店名称" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "HotelId", ExcelColumn = "酒店ID" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Date", ExcelColumn = "日期" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Bed", ExcelColumn = "房型名称" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Price", ExcelColumn = "含税价" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Breakfast", ExcelColumn = "早餐", }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Count", ExcelColumn = "库存" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CrawlerTime", ExcelColumn = "爬取时间", }); DataTable dtSource = FillDataTable(HotelInfo); //调用导出方法 ExcelHelper.MemoryStream(dtSource, excelconfig); } catch (Exception ex) { string log = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ex.ToString(); Console.WriteLine(log); } }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static void MemoryStream(DataTable dtSource, ExcelConfig excelConfig) { try { int colint = 0; bool cellValue = false; for (int i = 0; i < dtSource.Columns.Count;) { DataColumn column = dtSource.Columns[i]; if (excelConfig.ColumnEntity[colint].Column != column.ColumnName) { dtSource.Columns.Remove(column.ColumnName); } else { i++; colint++; } } XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; int rowIndex = 0; //判断是否已存在文件 if (File.Exists(excelConfig.FileName)) { FileStream fs = new FileStream(@excelConfig.FileName, FileMode.Open, FileAccess.ReadWrite); workbook = new XSSFWorkbook(fs); fs.Close(); sheet = workbook.GetSheetAt(0) as XSSFSheet; cellValue = true; rowIndex = sheet.LastRowNum + 1; } #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[dtSource.Columns.Count]; string[] arrColName = new string[dtSource.Columns.Count]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; // headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { //font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 foreach (DataColumn item in dtSource.Columns) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; arrColName[item.Ordinal] = item.ColumnName.ToString(); if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName); if (columnentity != null) { arrColName[item.Ordinal] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[item.Ordinal] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; //columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { // columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } columnStyle.Alignment = getAlignment(columnentity.Alignment); } } arryColumStyle[item.Ordinal] = columnStyle; } if (excelConfig.IsAllSizeColumn) { #region 根据列中最长列的长度取得列宽 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (arrColWidth[j] != 0) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } } #endregion } #endregion #region 填充数据 #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { if (rowIndex != 0) { //sheet = workbook.CreateSheet(); } #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(0); if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //设置列宽 //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); if (arrColWidth[column.Ordinal] > 255) { arrColWidth[column.Ordinal] = 254; } else { sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = arryColumStyle[column.Ordinal]; string drValue = row[column].ToString(); //排列序号 if (column.ColumnName == "Id") { drValue = (rowIndex - 1).ToString(); } if (drValue.Length >= 255) { drValue = drValue.Remove(255, drValue.Length - 255); } SetCell(newCell, dateStyle, column.DataType, drValue); } #endregion rowIndex++; } sheet.ForceFormulaRecalculation = true; //创建或打开文件填充数据 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); byte[] data = ms.ToArray(); using (FileStream fs1 = File.OpenWrite(@excelConfig.FileName)) { fs1.Write(data, 0, data.Length); fs1.Flush(); fs1.Close(); Console.WriteLine("下载完成!"); } ms.Flush(); } } catch (Exception e) { Console.WriteLine("下载失败,休眠2S!========================>" + e.ToString()); Thread.Sleep(2000); MemoryStream(dtSource, excelConfig); } }