Java Code Examples for org.apache.poi.ss.usermodel.Sheet#setColumnWidth()

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	sheet.setColumnWidth(EXTRACTED_TEXT_COL, EXTRACTED_TEXT_WIDTH*256);
	sheet.setDefaultColumnStyle(EXTRACTED_TEXT_COL, defaultStyle);
	Cell extractedHeaderCell = row.createCell(EXTRACTED_TEXT_COL);
	extractedHeaderCell.setCellStyle(headerStyle);
	extractedHeaderCell.setCellValue(EXTRACTED_TEXT_TITLE);
	for (int i = FIRST_LIC_ID_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, LIC_ID_COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Sheet sheet = wb.createSheet(sheetName);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
	Row dataRow = sheet.createRow(1);
	Cell ssVersionCell = dataRow.createCell(SPREADSHEET_VERSION_COL);
	ssVersionCell.setCellValue(SPDXSpreadsheet.CURRENT_VERSION);
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);		
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);

	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

/**
 * @param wb
 * @param reviewerSheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

protected void configureSheetColumnWidths( Sheet sheet, SlimSheetLayout sheetLayout, int columnCount ) {
  // Set column widths ..
  for ( int col = 0; col < columnCount; col++ ) {
    final double cellWidth = StrictGeomUtility.toExternalValue( sheetLayout.getCellWidth( col, col + 1 ) );
    final double poiCellWidth = ( cellWidth * getScaleFactor() );
    sheet.setColumnWidth( col, Math.min( 255 * 256, (int) poiCellWidth ) );
  }
}
 

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row headerRow = sheet.createRow(0);
	sheet.setColumnWidth(FIELD_COL, FIELD_COL_WIDTH*256);
	sheet.setDefaultColumnStyle(FIELD_COL, defaultStyle);
	Cell fieldCell = headerRow.createCell(FIELD_COL);
	fieldCell.setCellStyle(headerStyle);
	fieldCell.setCellValue(FIELD_HEADER_TEXT);
	
	sheet.setColumnWidth(EQUALS_COL, EQUALS_COL_WIDTH * 256);
	sheet.setDefaultColumnStyle(EQUALS_COL, defaultStyle);
	Cell equalsCell = headerRow.createCell(EQUALS_COL);
	equalsCell.setCellStyle(headerStyle);
	equalsCell.setCellValue(EQUALS_HEADER_TEXT);
	
	for (int i = FIRST_DOC_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS+FIRST_DOC_COL; i++) {
		sheet.setColumnWidth(i, COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = headerRow.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn( Sheet sheet, int columnToDelete ){
	int maxColumn = 0;
	for ( int r=0; r < sheet.getLastRowNum()+1; r++ ){
		Row	row	= sheet.getRow( r );
		
		// if no row exists here; then nothing to do; next!
		if ( row == null )
			continue;
		
		int lastColumn = row.getLastCellNum();
		if ( lastColumn > maxColumn )
			maxColumn = lastColumn;
		
		// if the row doesn't have this many columns then we are good; next!
		if ( lastColumn < columnToDelete )
			continue;
		
		for ( int x=columnToDelete+1; x < lastColumn + 1; x++ ){
			Cell oldCell	= row.getCell(x-1);
			if ( oldCell != null )
				row.removeCell( oldCell );
			
			Cell nextCell	= row.getCell( x );
			if ( nextCell != null ){
				Cell newCell	= row.createCell( x-1, nextCell.getCellType() );
				cloneCell(newCell, nextCell);
			}
		}
	}

	
	// Adjust the column widths
	for ( int c=0; c < maxColumn; c++ ){
		sheet.setColumnWidth( c, sheet.getColumnWidth(c+1) );
	}
}
 

private int buildGridExcelData(ReportGrid gridModel, Sheet sheet, int starDataRow, Map<String, CellStyle> styles) {
	CellStyle dataAlignLeftStyle = styles.get(GridStyleType.dataAlignLeftStyle.name());
	CellStyle dataAlignCenterStyle = styles.get(GridStyleType.dataAlignCenterStyle.name());
	CellStyle dataAlignRightStyle = styles.get(GridStyleType.dataAlignRightStyle.name());
	
	SimpleDateFormat sdf = ExportUtils.getSimpleDateFormat();
	List<ReportGridHeader> bottomGridExcelHeader = new ArrayList<ReportGridHeader>();
	this.calculateBottomColumnHeader(gridModel.getGridHeaderModelList(), bottomGridExcelHeader);
	
	List<Map<String, Object>> excelDatas = gridModel.getGridDataModel().getDatas();
	String treeColumn = gridModel.getGridDataModel().getTreeColumn();
	int excelDataIndex = 0;
	int rowSize = excelDatas.size();
	Cell cell;
	Row row;
	for (int rowNum = starDataRow; rowNum <= starDataRow + rowSize - 1; rowNum++) {
		row = sheet.createRow(rowNum);
		Map<String, Object> map = excelDatas.get(excelDataIndex);
		int j = 0;
		for (ReportGridHeader header : bottomGridExcelHeader) {
			Object value = map.get(header.getColumnName());
			int dataAlign = header.getDataAlign();
			cell = row.createCell(j);
			if (dataAlign == 1) {
				cell.setCellStyle(dataAlignCenterStyle);
			} else if (dataAlign == 2) {
				cell.setCellStyle(dataAlignRightStyle);
			} else {
				cell.setCellStyle(dataAlignLeftStyle);
			}
			if (value != null) {
				if (header.getColumnName().equalsIgnoreCase(treeColumn)) {
					int level = this.calculateIndentationCount(value.toString());
					cell.setCellStyle(new GridStyleBuilder().createIndentationCellStyle(sheet.getWorkbook(), level == 0 ? 0 : level * 2));
					cell.setCellValue(value.toString());
				} else {
					this.fillCellValue(cell, value, sdf);
				}

			} else {
				cell.setCellValue("");
			}
			sheet.setColumnWidth(cell.getColumnIndex(), header.getWidth() / 6 > 255 ? 254 * 256 : header.getWidth() / 6 * 256);
			j++;
		}
		excelDataIndex++;
	}
	return starDataRow + rowSize;

}
 

public void writeExcel() throws IOException {
    Workbook workbook = new XSSFWorkbook();

    try {
        Sheet sheet = workbook.createSheet("Persons");
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 4000);

        Row header = sheet.createRow(0);

        CellStyle headerStyle = workbook.createCellStyle();

        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFFont font = ((XSSFWorkbook) workbook).createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        headerStyle.setFont(font);

        Cell headerCell = header.createCell(0);
        headerCell.setCellValue("Name");
        headerCell.setCellStyle(headerStyle);

        headerCell = header.createCell(1);
        headerCell.setCellValue("Age");
        headerCell.setCellStyle(headerStyle);

        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true);

        Row row = sheet.createRow(2);
        Cell cell = row.createCell(0);
        cell.setCellValue("John Smith");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(20);
        cell.setCellStyle(style);

        File currDir = new File(".");
        String path = currDir.getAbsolutePath();
        String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";

        FileOutputStream outputStream = new FileOutputStream(fileLocation);
        workbook.write(outputStream);
    } finally {
        if (workbook != null) {
           
                workbook.close();
           
        }
    }
}
 

public InputStream exportExcel(String fileName, String[] headTitle,
		List<String> list) throws IOException {

	Workbook wb = new HSSFWorkbook();
	//FileOutputStream fileOut = new FileOutputStream(fileName);
	HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
	
	// 设置这些样式
	style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	// 生成一个字体
	HSSFFont font = (HSSFFont) wb.createFont();
	font.setColor(HSSFColor.VIOLET.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	// 把字体应用到当前的样式
	style.setFont(font);
	// 创建工作簿
	Sheet sheet = wb.createSheet("sheet1");
	// sheet.setDefaultColumnWidth((short)15);

	// 创建头部
	Row row = sheet.createRow((short) 0);
	for (int i = 0; i < headTitle.length; i++) {
		row.createCell(i).setCellValue(headTitle[i]);
	}
	// 填充数据
	for (int i = 0; i < list.size(); i++) {
		Row r = sheet.createRow((short) (i + 1));
		String[] strArray = list.get(i).split(",");
		for (int j = 0; j < headTitle.length; j++) {
			r.createCell(j).setCellValue(strArray[j]);
			if (strArray[j].length()>3) {
				sheet.setColumnWidth((short)j, 5000);
			}
		}
		
	}
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	try {
		wb.write(baos);
	} catch (IOException e) {
		e.printStackTrace();
	}
	byte[] ba = baos.toByteArray();
	ByteArrayInputStream excelStream = new ByteArrayInputStream(ba);
	return excelStream;

}
 

/**
 * 打印待发货的订单表
 *
 * @param order
 * @param consignmentName   发件人姓名
 * @param consignmentMobile 发货人手机号
 * @param consignmentAddr   发货地址
 */
@GetMapping("/waitingConsignmentExcel")
@PreAuthorize("@pms.hasPermission('order:order:waitingConsignmentExcel')")
public void waitingConsignmentExcel(Order order, @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date startTime,
                                    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date endTime, String consignmentName, String consignmentMobile,
                                    String consignmentAddr, HttpServletResponse response) {
    Long shopId = SecurityUtils.getSysUser().getShopId();
    order.setShopId(shopId);
    order.setStatus(OrderStatus.PADYED.value());
    List<Order> orders = orderService.listOrdersDetialByOrder(order, startTime, endTime);

    //通过工具类创建writer
    ExcelWriter writer = ExcelUtil.getWriter();
    Sheet sheet = writer.getSheet();
    sheet.setColumnWidth(0, 20 * 256);
    sheet.setColumnWidth(1, 20 * 256);
    sheet.setColumnWidth(2, 20 * 256);
    sheet.setColumnWidth(3, 60 * 256);
    sheet.setColumnWidth(4, 60 * 256);
    sheet.setColumnWidth(7, 60 * 256);
    sheet.setColumnWidth(8, 60 * 256);
    sheet.setColumnWidth(9, 60 * 256);
    // 待发货
    String[] hearder = {"订单编号", "收件人", "手机", "收货地址", "商品名称", "数量", "发件人姓名", "发件人手机号", "发货地址", "备注"};
    writer.merge(hearder.length - 1, "发货信息整理");
    writer.writeRow(Arrays.asList(hearder));

    int row = 1;
    for (Order dbOrder : orders) {
        UserAddrOrder addr = dbOrder.getUserAddrOrder();
        String addrInfo = addr.getProvince() + addr.getCity() + addr.getArea() + addr.getAddr();
        List<OrderItem> orderItems = dbOrder.getOrderItems();
        row++;
        for (OrderItem orderItem : orderItems) {
            // 第0列开始
            int col = 0;
            writer.writeCellValue(col++, row, dbOrder.getOrderNumber());
            writer.writeCellValue(col++, row, addr.getReceiver());
            writer.writeCellValue(col++, row, addr.getMobile());
            writer.writeCellValue(col++, row, addrInfo);
            writer.writeCellValue(col++, row, orderItem.getProdName());
            writer.writeCellValue(col++, row, orderItem.getProdCount());
            writer.writeCellValue(col++, row, consignmentName);
            writer.writeCellValue(col++, row, consignmentMobile);
            writer.writeCellValue(col++, row, consignmentAddr);
            writer.writeCellValue(col++, row, dbOrder.getRemarks());
        }
    }
    writeExcel(response, writer);
}