private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
// Title Style
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(titleFont);
style.setWrapText(true);
styles.put("title", style);
// Sub Title Style
Font subTitleFont = wb.createFont();
subTitleFont.setFontHeightInPoints((short) 14);
subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(subTitleFont);
style.setWrapText(true);
styles.put("subTitle", style);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cell", style);
return styles;
}
/** Create a library of cell styles */
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short) 11);
monthFont.setColor(IndexedColors.WHITE.getIndex());
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cell", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
private CellStyle getStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置单元格字体
Font headerFont = workbook.createFont(); // 字体
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(HSSFColor.BLACK.index);
headerFont.setFontName("宋体");
style.setFont(headerFont);
style.setWrapText(true);
// 设置单元格边框及颜色
style.setBorderBottom((short) 1);
style.setBorderLeft((short) 1);
style.setBorderRight((short) 1);
style.setBorderTop((short) 1);
style.setWrapText(true);
return style;
}
/**
* セルの縮小して全体を表示する
*
* @param nRow 行データ
* @return 有効列数
*/
public static void setShrinkToFitForCell(XSSFWorkbook wb, XSSFSheet sheet, int nRow, int nCol) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
XSSFCell cell = OoxmlUtil.getCellAnyway(row, nCol);
CellStyle styletmp = cell.getCellStyle();
CellStyle newStyletmp = wb.createCellStyle();
newStyletmp.cloneStyleFrom(styletmp);
newStyletmp.setWrapText(false); // 折り返して全体を表示する
newStyletmp.setShrinkToFit(true); // 縮小して全体を表示する
cell.setCellStyle(newStyletmp);
}
/**
* This is used to get the cell style of the body cells. If you want a custom style for the cells
* that say the attribute values of the entries in the list, customize this method.
*
* @param theWorkbook the Excel workbook we are working in
* @return the cell style for the body
*/
public static CellStyle GetBodyCellStyle(Workbook theWorkbook) {
CellStyle theStyle = theWorkbook.createCellStyle();
/*set the style to wrap the text and center vertical alignment
* NOTE: This is the code block where customization should be done
*/
theStyle.setWrapText(true);
theStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
theStyle.setAlignment(CellStyle.ALIGN_LEFT);
return theStyle;
}
/** @Description: 初始化内容行样式 */
private static void initContentCellStyle() {
contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
contentStyle.setFont(contentFont);
contentStyle.setBorderTop(CellStyle.BORDER_THIN);
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
contentStyle.setWrapText(true); // 字段换行
}
public CellStyle getHeaderStyle(XSSFWorkbook wb, Short fontSize) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
if (fontSize != null) {
font.setFontHeightInPoints(fontSize);
} else {
font.setFontHeightInPoints((short) 10);
}
// font.setColor(color.getIndex());
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setWrapText(true); // 自动换行
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
return style;
}
public void generate(
List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
throws IOException {
for (int i = 0; i < sheetNames.size(); i++) {
// Create a blank sheet
XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
// Create row object
XSSFRow row;
// This data needs to be written (Object[])
Map<Integer, String[]> contentMap = new TreeMap<>();
contentMap.put(1, headersList.get(i));
for (int j = 0; j < contents.get(i).size(); j++) {
contentMap.put(j + 2, contents.get(i).get(j));
}
// Iterate over data and write to sheet
Set<Integer> keyid = contentMap.keySet();
int rowid = 0;
for (Integer key : keyid) {
row = spreadsheet.createRow(rowid++);
String[] objectArr = contentMap.get(key);
int cellid = 0;
for (String obj : objectArr) {
Cell cell = row.createCell(cellid++);
CellStyle style = cell.getCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);
cell.setCellValue(obj);
}
}
// for (int c = 0; c < headersList.get(i).length; c++) {
// spreadsheet.autoSizeColumn(c);
// }
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File(fileName));
workbook.write(out);
out.close();
}
}
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
CellStyle style;
DataFormat format = wb.createDataFormat();
Font titleFont = wb.createFont();
titleFont.setFontName("Tahoma");
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font headFont = wb.createFont();
headFont.setFontName("Tahoma");
headFont.setFontHeightInPoints((short) 11);
headFont.setColor(IndexedColors.WHITE.getIndex());
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setWrapText(true);
styles.put("header", style);
Font groupFont = wb.createFont();
groupFont.setFontName("Tahoma");
groupFont.setFontHeightInPoints((short) 11);
groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
/* style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
*/ styles.put("cellleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0.00"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber2", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
styles.put("celltop", style);
return styles;
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}