private void processCell(Element tr, XSSFCell cell) {
int num = cell.getSheet().getNumMergedRegions();
// System.out.println(cell.getCTCell());
for (int i = 0; i < num; i++) {
CellRangeAddress c = cell.getSheet().getMergedRegion(i);
System.out.println(c.getFirstColumn());
;
System.out.println(c.getLastColumn());
System.out.println(c.getFirstRow());
System.out.println(c.getLastRow());
System.out.println();
System.out.println(cell.getRowIndex());
System.out.println(cell.getColumnIndex());
System.out.println("\n\n\n");
// System.out.println(cra);
}
// System.exit(0);
Element td = htmlDocumentFacade.createTableCell();
Object value;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
value = "\u00a0";
break;
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getNumericCellValue();
break;
default:
value = cell.getRichStringCellValue();
break;
}
if (value instanceof XSSFRichTextString) {
processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
td.setTextContent(value.toString());
} else {
processCellStyle(td, cell.getCellStyle(), null);
td.setTextContent(value.toString());
}
// System.err.println(value);
tr.appendChild(td);
}
/**
* 创建单元格
*
* @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 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 setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
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.getCellStyle().setFont(font);
}
/**
* @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;
}
}
/**
* 上線は太線のセル行を探す
*
* @param nRow 行データ
* @return 有効列数
*/
public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) {
int nRowIndex = nRow;
for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(0);
XSSFCellStyle styletmp = cell.getCellStyle();
short borderTopnum = styletmp.getBorderTop();
short borderBold = XSSFCellStyle.BORDER_MEDIUM;
if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) {
break;
}
}
}
return nRowIndex;
}
/**
* 创建单元格
*
* @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++;
}
}
/**
* 指定セルのセルスタイルを取得
*
* @param sheet シート
* @param nRow 行番号
* @param nColumn 列番号
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle getCellStyle(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
return cell.getCellStyle();
}
