JXL API wiki позволяет пользователям читать, писать, создавать и изменять листы в книге Excel (.xls) во время выполнения. Он не поддерживает формат .xlsx.
- JXL API поддерживает документы Excel с версиями
Excel 95, 97, 2000, XP, and 2003. Эти документы имеют расширение .xls
Используйте следующую функцию для копирования листа книги JXL.
public static void copySheetToWritableSheet(jxl.Sheet srcSheet, jxl.write.WritableSheet destSheet) throws JXLException {
int numrows = srcSheet.getRows();
int numcols = srcSheet.getColumns();
System.out.println("Rows:"+numrows+", Col:"+numcols);
for (int rowIdx = 0 ; rowIdx < numrows ; rowIdx++) {
for (int colIdx = 0 ; colIdx < numcols ; colIdx++) {
System.out.println("--- Rows:"+rowIdx+", Col:"+colIdx);
jxl.Cell srcCell = srcSheet.getCell(colIdx, rowIdx);
CellType type = srcCell.getType();
jxl.format.CellFormat format = srcCell.getCellFormat();
String cellValue = srcCell.getContents();
WritableCellFormat cf = null;
WritableCell newCell = null;
if (format != null) {
Colour backgroundColour = format.getBackgroundColour();
Font font = format.getFont();
WritableFont wf = new WritableFont(font);
cf = new WritableCellFormat(wf);
int value = backgroundColour.getValue();
String description = backgroundColour.getDescription();
System.out.println("Format Not Null Val:"+value+", Desc:"+description);
if (value != 192) { // Val: 192, Desc:default background [Dark Black]
cf.setBackground(backgroundColour);
}
cf.setAlignment(format.getAlignment());
cf.setBorder(jxl.format.Border.RIGHT, format.getBorderLine(Border.RIGHT));
cf.setBorder(Border.LEFT, format.getBorderLine(Border.LEFT));
cf.setBorder(Border.BOTTOM, format.getBorderLine(Border.BOTTOM));
cf.setBorder(Border.TOP, format.getBorderLine(Border.TOP));
cf.setWrap(format.getWrap());
if (type == CellType.NUMBER) {
newCell = new Number(colIdx, rowIdx, ((NumberCell) srcCell).getValue(), cf);
} else {
newCell = new Label(colIdx, rowIdx, cellValue, cf);
}
CellView cellView = srcSheet.getColumnView(colIdx);
destSheet.setColumnView(colIdx, cellView);
destSheet.addCell(newCell);
} else {
WritableFont wf = new WritableFont(ARIAL_10_PT);
// for position column we are not applying the display format
if (type == CellType.NUMBER) {
cf = new WritableCellFormat(wf, displayFormat);
newCell = new Number(colIdx, rowIdx, ((NumberCell) srcCell).getValue(), cf);
} else {
cf = new WritableCellFormat(wf);
newCell = new Label(colIdx, rowIdx, cellValue, cf);
}
CellView cellView = srcSheet.getColumnView(colIdx);
destSheet.setColumnView(colIdx, cellView);
destSheet.addCell(newCell); // https://stackoverflow.com/a/64675987/5081877
}
}
}
//Merge - MergedCells
Range[] mergedCells = srcSheet.getMergedCells();
for (int i = 0; i < mergedCells.length; i++) {
System.out.println("mergedCells:"+i);
Cell tl = mergedCells[i].getTopLeft();
Cell br = mergedCells[i].getBottomRight();
destSheet.mergeCells(tl.getColumn(), tl.getRow(), br.getColumn(), br.getRow());
}
SheetSettings srcSettings = srcSheet.getSettings();
SheetSettings destSettings = destSheet.getSettings();
destSettings.setZoomFactor(srcSettings.getZoomFactor());
}
Полный пример использования Java Excel API »2.6.12, Используемый образец файла - JXLWorkbook.xls.
public class JXL_XLS_Report {
static String filePath = "C:/Yash/",
sourceFile = filePath+"JXLWorkbook.xls", sourceFileSheetName = "FormatAbbrSheet",
destinationFile = filePath+"JXLWorkbook_Copy.xls";
public static void main(String[] args) throws Exception {
File sourceDST = new File(destinationFile);
jxl.write.WritableWorkbook workbook = Workbook.createWorkbook(sourceDST);
int numberOfSheets = workbook.getNumberOfSheets();
System.out.println("Number of Sheets:"+numberOfSheets);
// create the empty sheet
jxl.write.WritableSheet writableSheet = workbook.createSheet(sourceFileSheetName+"_777", numberOfSheets + 1);
File source = new File(sourceFile);
InputStream fileInStream = new FileInputStream(source);
jxl.Workbook templateWorkbook = Workbook.getWorkbook(fileInStream, getDefaultWorkbookSettings());
jxl.Sheet srcSheet = templateWorkbook.getSheet(sourceFileSheetName);
copySheetToWritableSheet(srcSheet, writableSheet);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setRationalization(false);
closeWorkbook(workbook);
}
static jxl.biff.DisplayFormat displayFormat = new NumberFormat("0.000");
static WritableFont ARIAL_10_PT = new WritableFont(WritableFont.ARIAL);
//static WritableFont DataFont = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
public static void copySheetToWritableSheet(jxl.Sheet srcSheet, jxl.write.WritableSheet destSheet) throws JXLException {
// ...
}
public static void closeWorkbook(WritableWorkbook workbook) throws IOException, JXLException {
if (workbook == null)
return;
if (workbook.getNumberOfSheets() == 0) {
workbook.createSheet("No data", 0); // otherwise pointer error
}
//Writes out the data held in this workbook in Excel format
workbook.write();
//Close and free allocated memory
workbook.close();
}
public static WorkbookSettings getDefaultWorkbookSettings() {
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding("ISO-8859-15");
workbookSettings.setLocale(Locale.GERMANY);
workbookSettings.setCharacterSet(1200);
workbookSettings.setExcelRegionalSettings("UK");
workbookSettings.setExcelDisplayLanguage("US");
workbookSettings.setPropertySets(false);
return workbookSettings;
}
public static void copyCellValue(Sheet srcSheet, int srcCol, int srcRow, WritableSheet destSheet, int destCol, int destRow) throws JXLException {
Cell srcCell = srcSheet.getCell(srcCol, srcRow);
CellType type = srcCell.getType();
WritableCell newCell = null;
if (type == CellType.LABEL) {
newCell = new Label(destCol, destRow, ((LabelCell) srcCell).getString());
} else if (type == CellType.NUMBER) {
newCell = new Number(destCol, destRow, ((NumberCell) srcCell).getValue());
} else if (type == CellType.BOOLEAN) {
newCell = new jxl.write.Boolean(destCol, destRow, ((BooleanCell) srcCell).getValue());
} else if (type == CellType.DATE) {
newCell = new DateTime(destCol, destRow, ((DateCell) srcCell).getDate());
} else if (type == CellType.EMPTY) {
newCell = new EmptyCell(destCol, destRow);
} else if (type == CellType.NUMBER_FORMULA
|| type == CellType.STRING_FORMULA
|| type == CellType.BOOLEAN_FORMULA) {
String formula = ((FormulaCell) srcCell).getFormula();
newCell = new Formula(destCol, destRow, formula);
} else {
String cellValue = srcCell.getContents();
newCell = new Label(destCol, destRow, cellValue);
}
// Set Column Size
CellView cellView = srcSheet.getColumnView(srcCol);
destSheet.setColumnView(srcCol, cellView);
destSheet.addCell(newCell);
}
public static void copyCellFormat(Sheet srcSheet, int srcCol, int srcRow, WritableSheet destSheet, int destCol, int destRow)throws JXLException {
CellFormat format = srcSheet.getCell(srcCol, srcRow).getCellFormat();
if (format == null) return;
WritableCell destCell = destSheet.getWritableCell(destCol, destRow);
if (destCell.getType() == CellType.EMPTY) {
WritableCell newCell = new Label(destCol, destRow, "");
newCell.setCellFormat(format);
destSheet.addCell(newCell);
} else {
destCell.setCellFormat(format);
}
}
}
person
Yash
schedule
11.11.2020
