public static void writeExcelFile(String fileLocation) {
try {
FileOutputStream fileOut = new FileOutputStream(fileLocation);
HSSFWorkbook workbook = new HSSFWorkbook();
Font bold = workbook.createFont(); // Create font
bold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Make font bold
CellStyle correctCell = workbook.createCellStyle();
correctCell.setFillForegroundColor(HSSFColor.GREEN.index);
correctCell.setFillBackgroundColor(HSSFColor.GREEN.index);
correctCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle incorrectCell = workbook.createCellStyle();
incorrectCell.setFillForegroundColor(HSSFColor.RED.index);
incorrectCell.setFillBackgroundColor(HSSFColor.RED.index);
incorrectCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle classificationCells = workbook.createCellStyle();
classificationCells.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationCells.setFillPattern(CellStyle.SOLID_FOREGROUND);
CellStyle attributeNameCells = workbook.createCellStyle();
attributeNameCells.setFont(bold);
CellStyle classificationAttributeCell = workbook.createCellStyle();
classificationAttributeCell.setFillForegroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillBackgroundColor(HSSFColor.YELLOW.index);
classificationAttributeCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
classificationAttributeCell.setFont(bold);
Sheet worksheet = workbook.createSheet("Results");
Row currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size() + 1; attribute++) {
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) {
currCell.setCellValue(metadataLL.get(attribute)[0]);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellStyle(classificationAttributeCell);
} else {
currCell.setCellStyle(attributeNameCells);
}
} else {
currCell.setCellValue("Guessed Classification");
currCell.setCellStyle(attributeNameCells);
}
}
int correct = 0;
int incorrect = 0;
for (int node = 0; node < testDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0;
attribute < metadataLL.size() + 2;
attribute++) // +1 for the row for guessed data +1 for the row that contains
{
Cell currCell = currRow.createCell(attribute);
if (attribute < metadataLL.size()) // Print testingData
{
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(actualClassifications.get(node));
currCell.setCellStyle(classificationCells);
classifierCompleted++;
} else {
currCell.setCellValue(testDataLL.get(node)[attribute - classifierCompleted]);
}
} else if (attribute == metadataLL.size()) // Print guessed classification
{
currCell.setCellValue(guessedClassifications.get(node));
if (guessedClassifications.get(node).compareTo(actualClassifications.get(node)) == 0) {
currCell.setCellStyle(correctCell);
correct++;
} else {
currCell.setCellStyle(incorrectCell);
incorrect++;
}
if (node == testDataLL.size() - 1) // If this is the last loop
{
double precentRight = (double) correct / (correct + incorrect);
currRow = worksheet.createRow(node + 2);
currCell = currRow.createCell(attribute);
currCell.setCellValue(precentRight);
if (precentRight > .90) {
correctCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(correctCell);
} else {
incorrectCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
currCell.setCellStyle(incorrectCell);
}
}
} else if (attribute
== metadataLL.size() + 1) // Print potential bad training data if the flag is true
{
if (unseenDataFlag.get(node)) {
currCell.setCellValue(
"This node an attribute value not in the training set, classifier selected is based on most frequent classifier. If laplacian smoothing is 1 or more this likely wont happen"); // TODO make this a bit shorter
}
}
}
}
worksheet = workbook.createSheet("Training Data");
currRow = worksheet.createRow(0);
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
currCell.setCellValue(metadataLL.get(attribute)[0]);
currCell.setCellStyle(attributeNameCells);
}
for (int node = 0; node < trainingDataLL.size(); node++) {
currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
int classifierCompleted = 0; // Necessary for if data does not end in classifier
for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
Cell currCell = currRow.createCell(attribute);
if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
currCell.setCellValue(knownClassifications.get(node));
classifierCompleted++;
} else {
currCell.setCellValue(trainingDataLL.get(node)[attribute - classifierCompleted]);
}
}
}
worksheet = workbook.createSheet("Likelihood");
currRow = worksheet.createRow(0);
int largestAttributeSize = 0;
for (int attribute = 0; attribute < classifier.size(); attribute++) {
if (classifier.get(attribute).size() > largestAttributeSize) {
largestAttributeSize = classifier.get(attribute).size();
}
}
// Label attributes along the top
for (int i = 0; i < metadataLL.size(); i++) {
if (i == 0) {
Cell currCell = currRow.createCell(i);
// currCell.setCellValue("Attributes");
currCell.setCellStyle(attributeNameCells);
} else {
Cell currCell = currRow.createCell(i);
currCell.setCellValue(
metadataLL
.get(i - 1)[0]); // -1 since the first cell does not contain a attribute name
currCell.setCellStyle(attributeNameCells);
}
}
// List possible classifications on the side and classification likelihoods at the end
for (int i = 0;
i
< (largestAttributeSize * (classificationTypes.size() + 1)
+ classificationTypes.size()
+ 1);
i++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{ // +classificationTypes.size() so we can list the classification types likelihood at the end
currRow = worksheet.createRow(i + 1); // +1 since first row is attribute names
Cell currCell = currRow.createCell(0);
int currentClassificationType =
i
% (classificationTypes.size()
+ 1); // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
// List the classification type of each row along the side
if (i
< largestAttributeSize
* (classificationTypes.size()
+ 1)) // +1 since the first row of each stride lists each attributes string of
// what occurrence the likelihoods are displaying
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say something later
} else if (currentClassificationType == j) {
currCell.setCellValue(
classificationTypes.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
} else // List the likelihood of each classification at the end
{
for (int j = 0;
j < classificationTypes.size() + 1;
j++) // +1 since the first row of each stride lists each attributes string of what
// occurrence the likelihoods are displaying
{
if (currentClassificationType == 0) {
// Do nothing for now may have it say value later
} else if (currentClassificationType == j) {
currCell.setCellValue(
"Likelihood of: "
+ classificationTypes.get(j - 1)
+ " is "
+ classificationLikelihood.get(
j - 1)); // -1 since the first cell does not contain a classification type
}
}
}
currCell.setCellStyle(attributeNameCells);
}
// List the data
for (int attribute = 0; attribute < classifier.size(); attribute++) {
for (int occurrences = 0; occurrences < classifier.get(attribute).size(); occurrences++) {
for (int classification = 0;
classification < classifier.get(attribute).get(occurrences).length;
classification++) {
currRow =
worksheet.getRow(
(occurrences * classifier.get(attribute).get(occurrences).length
+ classification)
+ 1); // +1 since first row is attribute names
Cell currCell =
currRow.createCell(
(attribute) + 1); // TODO figure out why this errors out at i:0 j:4 k:0
// largestAttributeSize:105 on kidney dataset
currCell.setCellValue(classifier.get(attribute).get(occurrences)[classification]);
}
}
}
workbook.write(fileOut);
workbook.close();
workbook.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
e.printStackTrace();
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to output file, is the output destination writelocked?");
e.printStackTrace();
System.exit(0);
}
}
private void saveExcelPoject(File file) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("timeplan");
// Заголовок в 0 строке
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Филиал");
cell = row.createCell(1);
cell.setCellValue("Город");
Calendar cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
cell = row.createCell(i + 2);
cell.setCellValue(sdf.format(cal.getTime()));
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
// sheet.setColumnWidth(0, 256);
// Цвета ячеек
CellStyle[] styles = new CellStyle[6];
styles[0] = wb.createCellStyle();
styles[0].setFillForegroundColor(HSSFColor.RED.index);
styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[1] = wb.createCellStyle();
styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[2] = wb.createCellStyle();
styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[3] = wb.createCellStyle();
styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[4] = wb.createCellStyle();
styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[5] = wb.createCellStyle();
styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);
short rowIdx = 0;
for (Region region : this.regions) {
row = sheet.createRow(++rowIdx);
cell = row.createCell(0);
cell.setCellValue(region.filial);
cell = row.createCell(1);
cell.setCellValue(region.name);
cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
short color = region.getDateColorIndex(cal.getTime());
if (color >= 0) {
cell = row.createCell(i + 2);
cell.setCellStyle(styles[color]);
}
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
}
try (FileOutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
}
@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 +++++++++++++");
}