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

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#setColumnWidth() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: SnippetSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 2
Source File: PerFileSheetV1d1.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 3
Source File: ReviewersSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 4
Source File: PackageInfoSheetV09d3.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 5
Source File: RelationshipsSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 6
Source File: PerFileSheetV2d2.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 7
Source File: ExtractedLicenseSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
/**
 * @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);
	}
}
 
Example 8
Source File: PackageInfoSheetV2d1.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 9
Source File: OriginsSheetV1d2.java    From tools with Apache License 2.0 6 votes vote down vote up
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);
}
 
Example 10
Source File: VerificationSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
/**
 * @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);
	}
}
 
Example 11
Source File: NonStandardLicensesSheetV1d1.java    From tools with Apache License 2.0 6 votes vote down vote up
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]);
	}
}
 
Example 12
Source File: ReviewerSheet.java    From tools with Apache License 2.0 6 votes vote down vote up
/**
 * @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);
	}
}
 
Example 13
Source File: ExcelPrinterBase.java    From pentaho-reporting with GNU Lesser General Public License v2.1 5 votes vote down vote up
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 ) );
  }
}
 
Example 14
Source File: PoiSheetUtility.java    From easypoi with Apache License 2.0 5 votes vote down vote up
/**
 * 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));
    }
}
 
Example 15
Source File: SnippetSheet.java    From tools with Apache License 2.0 5 votes vote down vote up
/**
 * @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);
	}
}
 
Example 16
Source File: SheetUtility.java    From openbd-core with GNU General Public License v3.0 5 votes vote down vote up
/**
 * 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) );
	}
}
 
Example 17
Source File: ExcelReportBuilder.java    From bdf3 with Apache License 2.0 4 votes vote down vote up
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;

}
 
Example 18
Source File: ExcelPOIHelper.java    From tutorials with MIT License 4 votes vote down vote up
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();
           
        }
    }
}
 
Example 19
Source File: PoiExport.java    From hrms with Apache License 2.0 4 votes vote down vote up
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;

}
 
Example 20
Source File: OrderController.java    From mall4j with GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * 打印待发货的订单表
 *
 * @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);
}