// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try {
if (!isSheetExist(sheetName)) return false;
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
sheet = workBook.getSheet(sheetName);
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);
for (int i = 0; i < getRowCount(sheetName); i++) {
row = sheet.getRow(i);
if (row != null) {
cell = row.getCell(colNum);
if (cell != null) {
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
int fristCol = 0;
int columnCount = 26;
String title = "";
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
/** 副标题每行放两个，一个居左，一个居右，所以除以2，多出来的一个，另起一行， */
for (int i = 0; i < rowCount; i++) {
row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
for (int j = 0; j < 2; j++) {
if (subHeadList.size() <= (i * 2 + j)) break;
style = workbook.createCellStyle();
title = subHeadList.get(i * 2 + j).toString();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
if (j == 0) {
/** 因为每行两个副标题，所以第一个标题占一半 */
fristCol = columnCount / 2;
cell = row.createCell(j); // 创建单元格
cell.setCellValue(title);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
} else {
/** 因为每行两个副标题，第一个标题占一半，第二个占剩下的所有单元格 */
fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
cell = row.createCell(columnCount / 2); // 创建单元格
cell.setCellValue(title);
// 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(
new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
}
}
}
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRow 行
* @param nColumn 列
* @param isBold 太字フラグ
* @param fontSize 文字サイズ
* @param fontHeight 行高
*/
public static void setCellStyleForLabel(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRow,
int nColumn,
boolean isBold,
short fontSize,
float fontHeight) {
assert sheet != null;
// style設定
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
if (isBold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字
}
font.setFontHeightInPoints((short) fontSize); // 文字サイズ
font.setFontName(DEFAULT_FONT_NAME);
style.setFont(font); // 文字太字 と 文字サイズ
style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準
style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め
style.setWrapText(true); // 折り返して全体を表示する
// セルに罫線を描画
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
row.setHeightInPoints(fontHeight); // 行高設定
}
/**
* セルに文字列を出力する
*
* @param cell 対象セル
* @param object 出力データ
* @param style セルスタイル
* @param zeroValue 値が0の時に設定する値
*/
private static void setData(XSSFCell cell, Object object, XSSFCellStyle style, String zeroValue) {
if (style != null) {
cell.setCellStyle(style);
}
if (object instanceof String) {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue((String) object);
} else if (object instanceof Integer) {
Integer integer = (Integer) object;
if (0 == integer) {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(zeroValue);
} else {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue((Integer) object);
}
} else if (object instanceof Double) {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
if (Double.isNaN((Double) object)) {
cell.setCellValue(zeroValue);
} else {
Double value = (Double) object;
if (0 == value.compareTo((Double) 0.0)) {
cell.setCellValue(zeroValue);
} else {
cell.setCellValue((Double) object);
}
}
}
}
// returns true if data is set successfully else false
public boolean setCellData(
String sheetName, String colName, int rowNum, String data, String url) {
// System.out.println("setCellData setCellData******************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
// System.out.println("A");
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum); // ashish
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
cell.setCellValue(data);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
// cell style for hyperlinks
// by default hypelrinks are blue and underlined
CellStyle hlink_style = workBook.createCellStyle();
XSSFFont hlink_font = workBook.createFont();
hlink_font.setUnderline(XSSFFont.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
// hlink_style.setWrapText(true);
XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* セルの縮小して全体を表示する
*
* @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);
}
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
cell.setCellStyle(style);
}
/**
* 创建单元格
*
* @param row
* @param style
* @param colList
* @param startCol
* @param rowset
*/
private void createColumn(
XSSFWorkbook workbook,
XSSFRow row,
XSSFCellStyle style,
List<String> colList,
int startCol,
EFRowSet rowset,
String type) {
XSSFCell cell = null;
for (int i = 0; i < colList.size(); i++) {
/** 创建单元格、设置列名称 */
cell = row.createCell(startCol);
cell.setCellStyle(style);
if (type.equals("N")) {
XSSFDataFormat df = workbook.createDataFormat();
cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称
} else {
if (colList.get(i).equals("F_CRFX")) {
if (rowset.getString(colList.get(i), "").equals("R")) {
cell.setCellValue("入库单");
} else if (rowset.getString(colList.get(i), "").equals("C")) {
cell.setCellValue("出库单");
} else if (rowset.getString(colList.get(i), "").equals("T")) {
cell.setCellValue("退库单");
} else if (rowset.getString(colList.get(i), "").equals("D")) {
cell.setCellValue("调拨单");
}
} else if (colList.get(i).equals("F_DJLX")) {
if (rowset.getString(colList.get(i), "").equals("R0")) {
cell.setCellValue("采购入库");
} else if (rowset.getString(colList.get(i), "").equals("R1")) {
cell.setCellValue("更换入库");
} else if (rowset.getString(colList.get(i), "").equals("T0")) {
cell.setCellValue("退库单");
} else if (rowset.getString(colList.get(i), "").equals("T1")) {
cell.setCellValue("材料退货");
} else if (rowset.getString(colList.get(i), "").equals("C0")) {
cell.setCellValue("正常出库");
} else if (rowset.getString(colList.get(i), "").equals("C1")) {
cell.setCellValue("借调出库");
} else if (rowset.getString(colList.get(i), "").equals("C2")) {
cell.setCellValue("被借调出库");
} else if (rowset.getString(colList.get(i), "").equals("D")) {
cell.setCellValue("仓库调拨");
}
} else {
cell.setCellValue(rowset.getString(colList.get(i), ""));
}
}
startCol++;
}
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++) {
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row,
// (short) j);
cell.setCellStyle(cs);
}
}
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(
XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
public static void setCellsx(
int row, int col, Object colValue, XSSFSheet sheet, XSSFCellStyle style) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
setCellValueOfCnX(cell, colValue);
cell.setCellStyle(style);
}
/** 创建一个副标题 */
private void createSubHeadCell(
XSSFWorkbook workbook,
XSSFRow row,
XSSFCellStyle style,
String title,
int rowIndex,
int colIndex) {
XSSFCell cell = row.createCell(colIndex); // 创建单元格
cell.setCellValue(title);
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1)); // 合并单元格
cell.setCellStyle(style);
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 セル結合
*
* @param workbook ワークブック
* @param sheet シート
* @param nRowStart 開始行
* @param nRowEnd 終了行
* @param nColumnStart 開始列
* @param nColumnEnd 終了列
* @param style 罫線style
*/
public static void setMerger(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRowStart,
int nRowEnd,
int nColumnStart,
int nColumnEnd,
XSSFCellStyle style) {
assert sheet != null;
sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd));
XSSFRow row = getRowAnyway(sheet, nRowStart);
XSSFCell cell = getCellAnyway(row, nColumnStart);
cell.setCellStyle(style);
}
/**
* 指定範囲にセルを新規作成
*
* @param sheet シート
* @param startRow 開始行番号
* @param endRow 終了行番号
* @param startColumn 開始列番号
* @param endColumn 終了列番号
* @param style セルスタイル
*/
public static void setStyle(
XSSFSheet sheet,
int startRow,
int endRow,
int startColumn,
int endColumn,
XSSFCellStyle style) {
for (int nRow = startRow; nRow <= endRow; nRow++) {
XSSFRow row = getRowAnyway(sheet, nRow);
for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
}
}
}
/**
* 创建标题，并设置字体、字号、加粗、颜色
*
* @param workbook
*/
private void setHeadCellFont(XSSFWorkbook workbook, String title) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
XSSFCell cell = row.createCell(0); // 创建单元格
XSSFFont font = workbook.createFont(); // 创建字体对象
font.setFontName(HSSFFont.FONT_ARIAL); // 字体
font.setFontHeightInPoints((short) 16); // 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row.setHeightInPoints(23); // 设置行高23像素
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是：起始行，结束行，起始列，结束列
cell.setCellValue(title); // 写入头标题
}
/**
* ハイパーリンクの設定
*
* @param sheet シート
* @param nRow 対象行番号
* @param nColumn 対象列番号
* @param value ハイパーリンクテキスト
* @param url ハイパーリンク先URL
*/
public static void setHyperLink(
XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
assert sheet != null;
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
hyperlink.setAddress(url);
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellValue(value);
cell.setHyperlink(hyperlink);
// ハイパーリンクテキストの装飾
XSSFFont font = workbook.createFont();
XSSFCellStyle style = workbook.createCellStyle();
// font.setColor(new XSSFColor(new Color(0, 0, 255)));
font.setUnderline(XSSFFont.U_SINGLE);
style.setFont(font);
cell.setCellStyle(style);
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRowStart 開始行
* @param nRowEnd 終了行
* @param nColumnStart 開始列
* @param nColumnEnd 終了列
* @param isBorder 罫線描画フラグ
* @param style 罫線style
*/
public static void setRowDataCellStyle(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRowStart,
int nRowEnd,
int nColumnStart,
int nColumnEnd,
boolean isBorder,
XSSFCellStyle style) {
assert sheet != null;
// Range内のすべてセルに罫線を描画
for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) {
XSSFRow row = getRowAnyway(sheet, rIndex);
for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) {
XSSFCell cell = getCellAnyway(row, cIndex);
cell.setCellStyle(style);
}
}
}
// returns true if column is created successfully
public boolean addColumn(String sheetName, String colName) {
// System.out.println("**************addColumn*********************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return false;
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
if (row == null) row = sheet.createRow(0);
// cell = row.getCell();
// if (cell == null)
// System.out.println(row.getLastCellNum());
if (row.getLastCellNum() == -1) cell = row.createCell(0);
else cell = row.createCell(row.getLastCellNum());
cell.setCellValue(colName);
cell.setCellStyle(style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 创建单元格
*
* @param row
* @param style
* @param colList
* @param startCol
* @param rowset
*/
private void createColumn(
XSSFWorkbook workbook,
XSSFRow row,
XSSFCellStyle style,
List<String> colList,
int startCol,
EFRowSet rowset,
String type) {
XSSFCell cell = null;
for (int i = 0; i < colList.size(); i++) {
/** 创建单元格、设置列名称 */
cell = row.createCell(startCol);
cell.setCellStyle(style);
if (type.equals("N")) {
XSSFDataFormat df = workbook.createDataFormat();
cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称
} else {
cell.setCellValue(rowset.getString(colList.get(i), ""));
}
startCol++;
}
}
private void generateExcelDoc(String docName) throws FileNotFoundException, IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet overall = workbook.createSheet("Overall");
XSSFRow row = overall.createRow(0);
XSSFCellStyle topStyle = workbook.createCellStyle();
topStyle.setAlignment(CellStyle.ALIGN_CENTER);
XSSFCell theme = row.createCell(0);
theme.setCellValue("Theme");
overall.autoSizeColumn(0);
XSSFCell occurs = row.createCell(1);
occurs.setCellValue("Occurrences");
overall.autoSizeColumn(1);
XSSFCell prev = row.createCell(2);
prev.setCellValue("Prevalence");
overall.autoSizeColumn(2);
theme.setCellStyle(topStyle);
occurs.setCellStyle(topStyle);
prev.setCellStyle(topStyle);
for (int i = 0; i < themes.size(); i++) {
XSSFRow r = overall.createRow((i + 1));
XSSFCell c = r.createCell(0);
c.setCellValue(themes.get(i).getName());
XSSFCell c1 = r.createCell(1);
c1.setCellValue(themes.get(i).getTotalOccurs());
XSSFCell c2 = r.createCell(2);
c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount));
}
// This could be done in the previous loop but since we don't need
// indices as much, we may as well use the cleaner for each loop
for (Theme t : themes) {
XSSFSheet themeSheet = workbook.createSheet(t.getName());
XSSFRow row1 = themeSheet.createRow(0);
XSSFCell keyword = row1.createCell(0);
keyword.setCellValue("Keyword");
keyword.setCellStyle(topStyle);
XSSFCell occ = row1.createCell(1);
occ.setCellValue("Occurrences");
occ.setCellStyle(topStyle);
XSSFCell themePrev = row1.createCell(2);
themePrev.setCellValue("Prevalence");
themePrev.setCellStyle(topStyle);
for (int i = 0; i < t.getKeywords().size(); i++) {
Keyword k = t.getKeywords().get(i);
XSSFRow r = themeSheet.createRow((i + 1));
XSSFCell c = r.createCell(0);
c.setCellValue(k.getName());
XSSFCell c1 = r.createCell(1);
c1.setCellValue(k.getNumOccurs());
XSSFCell c2 = r.createCell(2);
c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs()));
}
}
FileOutputStream output = new FileOutputStream(docName);
workbook.write(output);
output.close();
}
public static boolean writeArray2XLSXFile(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
XSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
XSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
XSSFRow row = sheet1.createRow(0);
int heandLine = 0;
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}
@GET
@Path("/getreport")
@Produces("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
public Response exportExcel(@QueryParam("from") String from, @QueryParam("to") String to)
throws Exception {
String params = from + to;
System.out.println(params);
File file = new File("src/main/resources/template.xlsx");
FileInputStream inputStream = new FileInputStream(file);
List<Customer> customerList = null;
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
// CellStyle dateStyle = wb.createCellStyle();
// CreationHelper createHelper = wb.getCreationHelper();
// dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(
// "yyyy/mm/dd hh:mm"));
if (from != null && to != null) {
Date startDate = Utils.formatDate(from);
Date endDate = Utils.formatDate(to);
customerList = dao.findByDate(startDate, endDate);
// wb = Utils.fillReport(wb, customerList);
} else {
customerList = dao.findAll();
// wb = Utils.fillReport(wb, customerList);
}
for (int i = 0; i < customerList.size(); i++) {
XSSFRow row = sheet.createRow(i + 3);
Customer customer = customerList.get(i);
for (int j = 0; j < 10; j++) {
XSSFCell cell = row.createCell(j);
switch (j) {
case 0:
cell.setCellValue(customer.getCreated());
cell.setCellStyle(sheet.getColumnStyle(j));
// cell.setCellStyle(dateStyle);
break;
case 1:
cell.setCellValue(customer.getName());
break;
case 2:
cell.setCellValue(customer.getEmail());
break;
case 3:
cell.setCellValue(customer.getPhone());
break;
case 4:
cell.setCellValue(customer.getOrganisation());
break;
case 5:
cell.setCellValue(customer.getStartDate());
cell.setCellStyle(sheet.getColumnStyle(j));
break;
case 6:
cell.setCellValue(customer.getEndDate());
cell.setCellStyle(sheet.getColumnStyle(j));
break;
case 7:
cell.setCellValue(customer.getNumberOfPeople());
break;
case 8:
cell.setCellValue(customer.isCatering());
break;
case 9:
cell.setCellValue(customer.getAdditionalComments());
break;
default:
break;
}
}
}
StreamingOutput stream =
new StreamingOutput() {
public void write(OutputStream output) throws IOException, WebApplicationException {
try {
wb.write(output);
wb.close();
} catch (Exception e) {
throw new WebApplicationException(e);
}
}
};
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("YYYY_MM_dd");
String dateString = sdf.format(date);
return Response.ok(stream)
.header(
"content-disposition",
"attachment; filename = space-bookings-export_" + dateString + ".xlsx")
.build();
}
public static boolean convertTextFile2XLSXFile(
String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
String line = br.readLine();
if (line == null) {
return false;
}
String[] cells1 = Util.tokenize(line, '\t');
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = cells1.length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
if (hasHead) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(headStyle);
}
} else {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(cells1[i]);
cell.setCellStyle(bodyStyle);
}
}
rowIndex++;
while ((line = br.readLine()) != null) {
cells1 = Util.tokenize(line, '\t');
row = sheet1.createRow((rowIndex));
columnNum = cells1.length;
if (indexKey >= 0) {
if (lastKey == null && cells1[indexKey] != null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey != null && cells1[indexKey] == null) {
lastKey = cells1[indexKey];
switcher *= -1;
} else if (lastKey == null && cells1[indexKey] == null) {
} else {
if (!lastKey.equals(cells1[indexKey])) {
switcher *= -1;
lastKey = cells1[indexKey];
}
}
} else {
switcher = 1;
}
// System.out.println(cells1[0]);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (cells1[j] != null) {
if (Util.isNumeric(cells1[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(cells1[j]));
} else {
cell.setCellValue(cells1[j]);
}
} else {
cell.setCellValue(".");
}
}
rowIndex++;
}
br.close();
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(outFileName);
wb.write(fileOut);
fileOut.close();
return true;
}
public static boolean writeArray2XLSXSheet(
XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
}
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
XSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
XSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell((short) i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}
public static boolean writeMultArray2XLSXFile(
String fileName,
List<List<String[]>> arrys,
List<String> sheetLabels,
boolean hasHead,
int indexKey)
throws Exception {
if (arrys.isEmpty()) {
System.err.println("No input data!");
return false;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle headStyle = wb.createCellStyle();
// apply custom font to the text in the comment
XSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
XSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
String lastKey = null;
int switcher = -1;
XSSFCell cell = null;
String[] titleNames = null;
int d = 0;
for (List<String[]> arry : arrys) {
XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
}
int rowIndex = 0;
// create titile row
XSSFRow row = sheet1.createRow(rowIndex);
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
int rowNum = arry.size();
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow((i));
String[] line = (String[]) arry.get(i);
columnNum = line.length;
if (indexKey >= 0) {
if (lastKey == null && line[indexKey] != null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey != null && line[indexKey] == null) {
lastKey = line[indexKey];
switcher *= -1;
} else if (lastKey == null && line[indexKey] == null) {
} else {
if (!lastKey.equals(line[indexKey])) {
switcher *= -1;
lastKey = line[indexKey];
}
}
} else {
switcher = 1;
}
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (switcher > 0) {
cell.setCellStyle(bodyStyle);
} else {
cell.setCellStyle(markedBodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
d++;
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(fileName);
wb.write(fileOut);
fileOut.close();
return true;
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 四角の範囲に周囲太線、中は細線を描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRowStart 開始行
* @param nRowEnd 終了行
* @param nColumnStart 開始列
* @param nColumnEnd 終了列
* @param isBorder 罫線描画フラグ
* @param styleMap 罫線style
*/
public static void setTableDataCellStyle(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRowStart,
int nRowEnd,
int nColumnStart,
int nColumnEnd,
boolean isBorder,
Map<String, XSSFCellStyle> styleMap) {
assert sheet != null;
// Range内のすべてセルに罫線を描画
for (int rIndex = nRowStart; rIndex <= nRowEnd; rIndex++) {
XSSFRow row = getRowAnyway(sheet, rIndex);
for (int cIndex = nColumnStart; cIndex <= nColumnEnd; cIndex++) {
XSSFCell cell = getCellAnyway(row, cIndex);
cell.setCellStyle(styleMap.get("normal"));
}
}
// 初行のTopのみ太罫線
XSSFRow row = getRowAnyway(sheet, nRowStart);
XSSFCell cell = getCellAnyway(row, nColumnStart);
cell.setCellStyle(styleMap.get("isTopAndLeft"));
for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) {
cell = getCellAnyway(row, cIndex);
cell.setCellStyle(styleMap.get("isTop"));
}
cell = getCellAnyway(row, nColumnEnd);
cell.setCellStyle(styleMap.get("isTopAndRight"));
// 間の行
for (int cIndexCenter = nRowStart + 1; cIndexCenter < nRowEnd; cIndexCenter++) {
row = getRowAnyway(sheet, cIndexCenter);
cell = getCellAnyway(row, nColumnStart);
cell.setCellStyle(styleMap.get("isLeft"));
cell = getCellAnyway(row, nColumnEnd);
cell.setCellStyle(styleMap.get("isRight"));
}
// 最後の行のBottomのみ太罫線
XSSFRow rowEnd = getRowAnyway(sheet, nRowEnd);
cell = getCellAnyway(rowEnd, nColumnStart);
cell.setCellStyle(styleMap.get("isBottomAndLeft"));
for (int cIndex = nColumnStart + 1; cIndex < nColumnEnd; cIndex++) {
cell = getCellAnyway(rowEnd, cIndex);
cell.setCellStyle(styleMap.get("isBottom"));
}
cell = getCellAnyway(rowEnd, nColumnEnd);
cell.setCellStyle(styleMap.get("isBottomAndRight"));
// CellRangeAddress region=new
// CellRangeAddress(nRowStart,nRowEnd,nColumnStart,nColumnEnd);
// short border=XSSFCellStyle.BORDER_MEDIUM;//太罫線
// RegionUtil.setBorderTop(border,region,sheet,workbook);
// RegionUtil.setBorderBottom(border,region,sheet,workbook);
// RegionUtil.setBorderLeft(border, region, sheet, workbook);
// RegionUtil.setBorderRight(border, region, sheet, workbook);
}
/**
* 创建列
*
* @param workbook
*/
private void exportColumnData(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle topStyle = setColumnTopBorder(workbook);
XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1;
row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
setColumnBorder(style, 0); // 设置列边框
workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(0);
cell.setCellValue("会计期间");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0));
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("单据编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1));
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("分录编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("材料入库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellStyle(topStyle);
cell = row.createCell(5);
cell.setCellStyle(topStyle);
cell = row.createCell(6);
cell.setCellStyle(topStyle);
cell = row.createCell(7);
cell.setCellStyle(topStyle);
cell = row.createCell(8);
cell.setCellStyle(topStyle);
cell = row.createCell(9);
cell.setCellValue("材料出库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14));
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellStyle(topStyle);
cell = row.createCell(11);
cell.setCellStyle(topStyle);
cell = row.createCell(12);
cell.setCellStyle(topStyle);
cell = row.createCell(13);
cell.setCellStyle(topStyle);
cell = row.createCell(14);
cell.setCellStyle(topStyle);
cell = row.createCell(15);
cell.setCellValue("材料编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15));
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("材料名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16));
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("规格型号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17));
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("计量单位");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18));
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("供应商编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19));
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("供应商名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20));
cell.setCellStyle(style);
cell = row.createCell(21);
cell.setCellValue("出入方向");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21));
cell.setCellStyle(style);
cell = row.createCell(22);
cell.setCellValue("单据类型");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22));
cell.setCellStyle(style);
cell = row.createCell(23);
cell.setCellValue("材料单价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25));
cell.setCellStyle(style);
cell = row.createCell(24);
cell.setCellValue("材料数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23));
cell.setCellStyle(style);
cell = row.createCell(25);
cell.setCellValue("材料总价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24));
cell.setCellStyle(style);
row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题
/** 创建单元格、设置列名称、合并单元格 */
// 材料入库信息
cell = row.createCell(3);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
// 材料出库信息
cell = row.createCell(9);
cell.setCellValue("产品名称");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
for (int i = 0; i < 3; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
for (int i = 15; i < 26; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
}
/**
* 创建列
*
* @param workbook
*/
private void exportColumnData(XSSFWorkbook workbook) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle topStyle = setColumnTopBorder(workbook);
XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
row = workbook.getSheetAt(0).createRow(5); // 创建一个行对象 列标题
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
setColumnBorder(style, 0); // 设置列边框
workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(0);
cell.setCellValue("材料编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 0, 0));
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("材料名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 1, 1));
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("型号规格");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 2, 2));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("单位");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 3, 3));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("申请数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 4, 4));
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("入库数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 5, 5));
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("出库数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellStyle(topStyle);
cell = row.createCell(8);
cell.setCellStyle(topStyle);
cell = row.createCell(9);
cell.setCellValue("退货数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 9, 10));
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellStyle(topStyle);
cell = row.createCell(11);
cell.setCellValue("调拨数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 11, 11));
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("库存数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 12, 12));
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("领用数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 13, 13));
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("领用总价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 14, 14));
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue("库存情况");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 15, 15));
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("备注");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 16, 16));
cell.setCellStyle(style);
row = workbook.getSheetAt(0).createRow(6); // 创建一个行对象 列标题
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(6);
cell.setCellValue("正常领用");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("借调");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("被借调");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("材料退货");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("厂商退货");
cell.setCellStyle(style);
for (int i = 0; i < 5; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
for (int i = 11; i < 16; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
}
private void renderCell(
BandElement bandElement,
String bandName,
Object value,
int gridRow,
int sheetRow,
int sheetColumn,
int rowSpan,
int colSpan,
boolean image) {
if (bandElement instanceof ReportBandElement) {
colSpan = 1;
}
XSSFCellStyle cellStyle =
buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);
// if we have a subreport on the current grid row we have to take care of the sheetColumn
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
&& (gridRow == prevSubreportFirstRow)
&& (prevSubreportLastColumn != -1)) {
sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
}
XSSFCell c = xlsRow.createCell(sheetColumn);
if (image) {
if ((value == null) || "".equals(value)) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND));
} else {
try {
ImageBandElement ibe = (ImageBandElement) bandElement;
byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
XSSFClientAnchor anchor =
new XSSFClientAnchor(
0,
0,
0,
0,
(short) sheetColumn,
sheetRow,
(short) (sheetColumn + colSpan),
(sheetRow + rowSpan));
int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
// image is created over the cells, so if it's height is bigger we set the row height
short height = xlsRow.getHeight();
int realImageHeight = getRealImageSize((String) value)[1];
if (ibe.isScaled()) {
realImageHeight = ibe.getHeight();
}
short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
boolean doResize = false;
if (imageHeight > height) {
xlsRow.setHeight(imageHeight);
} else {
doResize = true;
}
Picture picture = patriarch.createPicture(anchor, index);
if (doResize) {
picture.resize();
}
anchor.setAnchorType(2);
} catch (Exception ex) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
}
}
if (cellStyle != null) {
c.setCellStyle(cellStyle);
}
} else {
if (bandElement instanceof HyperlinkBandElement) {
Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL);
link.setAddress(hyp.getUrl());
c.setHyperlink(link);
c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText()));
c.setCellType(XSSFCell.CELL_TYPE_STRING);
} else if (bandElement instanceof ReportBandElement) {
Report report = ((ReportBandElement) bandElement).getReport();
ExporterBean eb = null;
try {
eb = getSubreportExporterBean(report, true);
XlsxExporter subExporter = new XlsxExporter(eb, cellStyle);
subExporter.export();
XSSFSheet subreportSheet = subExporter.getSubreportSheet();
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
&& (gridRow == prevSubreportFirstRow)) {
// other subreports on the same header line after the first
sheetColumn = prevSubreportLastColumn;
sheetRow -= addedPageRows;
pageRow -= addedPageRows;
addedPageRows = 0;
} else {
addedPageRows = subreportSheet.getLastRowNum();
pageRow += addedPageRows;
// if subreport is not on the first column we merge all cells in the columns before,
// between the rows subreport occupies
if (sheetColumn > 0) {
for (int i = 0; i <= sheetColumn - 1; i++) {
CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
regions.add(new XlsxRegion(cra, null));
}
}
}
int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
prevSubreportFirstRow = gridRow;
prevSubreportFirstColumn = sheetColumn;
prevSubreportLastColumn = sheetColumn + cols;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if ((eb != null) && (eb.getResult() != null)) {
eb.getResult().close();
}
}
} else if (bandElement instanceof ImageColumnBandElement) {
try {
ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
String v = StringUtil.getValueAsString(value, null);
if (StringUtil.BLOB.equals(v)) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB));
} else {
byte[] imageD = StringUtil.decodeImage(v);
byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
XSSFClientAnchor anchor =
new XSSFClientAnchor(
0,
0,
0,
0,
(short) sheetColumn,
sheetRow,
(short) (sheetColumn + colSpan),
(sheetRow + rowSpan));
int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
// image is created over the cells, so if it's height is bigger we set the row height
short height = xlsRow.getHeight();
int realImageHeight = getRealImageSize(imageBytes)[1];
if (icbe.isScaled()) {
realImageHeight = icbe.getHeight();
}
short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
if (imageHeight > height) {
xlsRow.setHeight(imageHeight);
}
Picture picture = patriarch.createPicture(anchor, index);
picture.resize();
anchor.setAnchorType(2);
}
} catch (Exception e) {
e.printStackTrace();
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
}
} else {
if (value == null) {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
c.setCellValue(wb.getCreationHelper().createRichTextString(""));
} else if (value instanceof Number) {
c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(((Number) value).doubleValue());
} else {
String pattern = null;
if (bandElement instanceof FieldBandElement) {
FieldBandElement fbe = (FieldBandElement) bandElement;
pattern = fbe.getPattern();
}
if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
Date date;
if (value instanceof java.sql.Date) {
date = new Date(((java.sql.Date) value).getTime());
} else {
date = (java.sql.Timestamp) value;
}
if (cellStyle != null) {
if (pattern == null) {
// use default pattern if none selected
Locale locale = Locale.getDefault();
pattern =
((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale))
.toPattern();
} else {
pattern = StringUtil.getI18nString(pattern, getReportLanguage());
}
cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
}
c.setCellValue(date);
} else {
c.setCellType(XSSFCell.CELL_TYPE_STRING);
String text = StringUtil.getValueAsString(value, pattern);
if ((bandElement != null) && bandElement.isWrapText()) {
// try to interpret new line characters
// \\n is used here to be possible to add in designer grid cell with \n
if (text.contains("\\n")
|| text.contains("\n")
|| text.contains("\r")
|| text.contains("\r\n")) {
String crLf = Character.toString((char) 13) + Character.toString((char) 10);
int lines = countLines(text);
if (text.contains("\r\n")) {
text = text.replaceAll("\r\n", crLf);
} else {
text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
}
c.setCellValue(text);
cellStyle.setWrapText(true);
xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3));
} else {
c.setCellValue(wb.getCreationHelper().createRichTextString(text));
}
} else {
c.setCellValue(wb.getCreationHelper().createRichTextString(text));
}
}
}
}
if (cellStyle != null) {
if (bandElement != null) {
cellStyle.setRotation(bandElement.getTextRotation());
}
if (!(bandElement instanceof ReportBandElement)) {
c.setCellStyle(cellStyle);
}
}
if ((rowSpan > 1) || (colSpan > 1)) {
CellRangeAddress cra =
new CellRangeAddress(
sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1);
Border beBorder = bandElement.getBorder();
if (hasRowRenderConditions(bandElement, gridRow, value)) {
// for row render conditions we must keep the row border
beBorder = border;
}
regions.add(new XlsxRegion(cra, beBorder));
}
}
}