private void loadData() {
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
if (i % 2 == 0) {
Row rowID = sheet.getRow(i);
Row rowName = sheet.getRow(i + 1);
for (int j = 0; j < 15; j++) {
Cell cellID = rowID.getCell(j);
Cell cellName = rowName.getCell(j);
String name = WorkBookUtil.getCellValue(cellName);
String cellValue = WorkBookUtil.getCellValue(cellID);
if (name == null || name.equalsIgnoreCase("")) {
continue;
}
int parseInt = Integer.parseInt(cellValue);
if (catalogUsers.containsValue(name)) {
repeatUsers.put(parseInt, name);
}
if (cellName.getCellStyle().getFillPattern() == CellStyle.SOLID_FOREGROUND) {
colorUsers.put(parseInt, name);
}
catalogUsers.put(parseInt, name);
}
}
}
}
private Result populateLoansTable(Sheet savingsTransactionSheet) {
Result result = new Result();
Workbook workbook = savingsTransactionSheet.getWorkbook();
CellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat("dd/mm/yy");
dateCellStyle.setDataFormat(df);
int rowIndex = 1;
Row row;
Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
try {
for (CompactSavingsAccount savingsAccount : savings) {
row = savingsTransactionSheet.createRow(rowIndex++);
writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
writeDate(
LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
row,
savingsAccount.getTimeline().getActivatedOnDate().get(2)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(1)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(0),
dateCellStyle);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
return result;
}
public List<ImportNeolixVO> extractNeolixInfo(File file) {
List<ImportNeolixVO> neolix = new LinkedList<>();
// 解析文件
Workbook workBook = null;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e) {
throw new RuntimeException("没有找到对应的文件", e);
}
try {
workBook = WorkbookFactory.create(fis);
Sheet sheet = workBook.getSheetAt(0);
int lastRowNumber = sheet.getLastRowNum();
Row rowTitle = sheet.getRow(0);
if (rowTitle == null) {
neolix = null;
return neolix;
}
// 从第1行开始（不算标题）
for (int i = 1; i <= lastRowNumber; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
ImportNeolixVO vo = convert(row);
neolix.add(vo);
}
} catch (Exception e) {
System.out.println(e.toString());
neolix = null;
}
return neolix;
}
/**
* 获取销售记录
*
* @param productSheet
* @return
*/
private int getSaleRecordsFormXSheet(Sheet salesSheet) {
int count = 0;
if (salesSheet == null) return 0;
try {
int rowStart = salesSheet.getFirstRowNum();
int rowEnd = salesSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = salesSheet.getRow(i);
SaleRecord sr = new SaleRecord();
// 日期
String datestr = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(datestr)) continue;
Date date = null;
try {
date = new SimpleDateFormat("yyyy-MM-dd").parse(datestr);
} catch (Exception e) {
continue;
}
String struser = String.valueOf(getXssCellData(row.getCell(1)));
// if have not user info discard this record
if ("".equals(struser)) continue;
int user = Integer.parseInt(struser);
String strproduct = String.valueOf(getXssCellData(row.getCell(2)));
// if have not user info discard this record
if ("".equals(strproduct)) continue;
int product = Integer.parseInt(strproduct);
// 购买价格
String pricestr = (String) getXssCellData(row.getCell(3));
double price = 0d;
if (!"".equals(pricestr)) {
price = Double.parseDouble(pricestr);
}
// 购买量
String numstr = (String) getXssCellData(row.getCell(3));
double num = 0d;
if (!"".equals(numstr)) {
num = Double.parseDouble(numstr);
}
sr.setNum(num);
sr.setPrice(price);
sr.setProduct(product);
sr.setUser(user);
sr.setSaledate(date);
simpleDao.createEntity(sr);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getSaleRecordsFormXSheet exception !");
}
return count;
}
public void loadBeachLocations() {
try {
FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls"));
workbook = new HSSFWorkbook(file);
Sheet sheet = workbook.getSheet("BEACH SUMMARY");
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // Skip the header row.
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String code = row.getCell(1).getStringCellValue().trim();
System.out.println(code);
if (!code.isEmpty()) {
String beachname = row.getCell(1).getStringCellValue().trim();
String county = row.getCell(2).getStringCellValue().trim();
String description = row.getCell(1).getStringCellValue().trim() + "Beach";
System.out.println(beachname + ":" + county + ":" + description);
Beach beach = new Beach(beachname, description, county);
beach.saveBeach();
}
}
JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations....");
} catch (IOException ex) {
ex.printStackTrace();
Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void loadData(Data data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream file = new FileInputStream(new File(path));
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
try {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList<String> arrayRow = new ArrayList<String>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
testDataType(cell, arrayRow);
}
data.getData().add(arrayRow);
}
} catch (Exception e) {
e.printStackTrace();
}
workbook.close();
file.close();
sheet = null;
workbook = null;
file = null;
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
public static Date getBaseDateFromExcelWithPoi(File file) {
InputStream in = null;
try {
in = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0);
Name name = workbook.getName("雷線基準日");
CellReference cellRef = new CellReference(name.getRefersToFormula());
Row row = sheet.getRow(cellRef.getRow());
Cell baseDateCell = row.getCell(cellRef.getCol());
// System.out.println("cellが日付か:"
// + PoiUtil.isCellDateFormatted(baseDateCell));
Date baseDate = baseDateCell.getDateCellValue();
return baseDate;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (in != null)
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
@Test
public void report_FacilityUsage_asOperator2() {
final ReportParameters params = baseParams();
params.interval = 3 * 60;
registerMockFacilityUsages(facility1, apiUser);
registerMockFacilityUsages(facility2, apiUser2);
final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", operator2User);
// If this succeeds, the response was a valid excel file
final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
assertThat(getSheetNames(workbook)).containsExactly("Käyttöasteraportti", "Selite");
final Sheet usages = workbook.getSheetAt(0);
// Header and one for each usage type
assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(3);
// Only operator2 visible
assertThat(getDataFromColumn(usages, 3))
.containsOnly("Operaattori", operator2.name.fi)
.doesNotContain(operator1.name.fi);
final List<String> headers = getDataFromRow(usages, 0);
assertThat(headers.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, headers.size()))
.containsExactly("00:00", "03:00", "06:00", "09:00", "12:00", "15:00", "18:00", "21:00");
// Get the hourly utilizations for CAR
// Results are not interpolated.
final List<String> row = getDataFromRow(usages, 1);
assertThat(row.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, row.size()))
.containsExactly("24", "24", "24", "24", "0", "0", "0", "24");
}
public void make(File source, DataSet header, DataSet master) throws Exception {
this.header = header;
this.master = master;
if (header == null || master == null) throw new Exception("Dataset is empty");
long t = System.currentTimeMillis();
InputStream inp = new FileInputStream(source);
Workbook oldBook = WorkbookFactory.create(inp);
Sheet oldSheet = oldBook.getSheetAt(0);
Workbook newBook = new HSSFWorkbook();
Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());
init(newBook);
process(oldSheet, newSheet);
File target = File.createTempFile("libra", ".xls");
target.deleteOnExit();
FileOutputStream fileOut = new FileOutputStream(target);
newBook.write(fileOut);
fileOut.close();
oldBook.close();
inp.close();
Desktop.getDesktop().open(target);
System.out.println(System.currentTimeMillis() - t);
}
// ����Excel
public String ExcelExport() throws Exception {
HttpServletRequest request = ServletActionContext.getRequest();
String ids = request.getParameter("ids");
List<Dise> list = new ArrayList<Dise>();
String[] array = ids.split(",");
int[] id = new int[array.length];
for (int i = 0; i < id.length; i++) {
Dise dise = DiseService.findById(Integer.valueOf(array[i]));
list.add(dise);
}
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("ѧ����Ϣ");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("ѧ��");
row.createCell(1).setCellValue("����");
row.createCell(2).setCellValue("����");
row.createCell(3).setCellValue("�Ա�");
row.createCell(4).setCellValue("��ַ");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
for (int i = 1; i <= list.size(); i++) {
Dise stu = list.get(i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(stu.getIds());
row.createCell(1).setCellValue(stu.getName());
row.createCell(2).setCellValue(stu.getA());
row.createCell(3).setCellValue(stu.getB());
row.createCell(4).setCellValue(stu.getC());
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
excelFile = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return "excel";
}
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Specialite> listSpecialite = new ArrayList<>();
FileInputStream file = new FileInputStream(new File(excelFilePath));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Specialite sp = new Specialite();
Admin admin = new Admin();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 0:
sp.setIntitule((String) getCellValue(nextCell));
break;
}
}
listSpecialite.add(sp);
}
file.close();
return listSpecialite;
}
@Override
public List<String> getHeaderColumns() {
if (headerColumns == null) {
headerColumns = new ArrayList<>();
Row row = dataSheet.getRow(0);
int colCount = row != null ? row.getLastCellNum() : 0;
if (row == null || colCount == 0) {
LogMgr.logError(
"ExcelReader.getHeaderColumns()",
"Cannot retrieve column names because no data is available in the first row of the sheet: "
+ dataSheet.getSheetName(),
null);
String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
messages.append(msg);
messages.appendNewLine();
return headerColumns;
}
for (int i = 0; i < colCount; i++) {
Cell cell = row.getCell(i);
Object value = getCellValue(cell);
if (value != null) {
headerColumns.add(value.toString());
} else {
headerColumns.add("Col" + Integer.toString(i));
}
}
}
return headerColumns;
}
private void initActiveSheet() {
if (dataFile == null) return;
if (sheetIndex > -1) {
dataSheet = dataFile.getSheetAt(sheetIndex);
if (dataSheet == null) {
throw new IndexOutOfBoundsException(
"Sheet with index "
+ sheetIndex
+ " does not exist in file: "
+ inputFile.getFullPath());
}
} else if (sheetName != null) {
dataSheet = dataFile.getSheet(sheetName);
if (dataSheet == null) {
throw new IllegalArgumentException(
"Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
}
} else {
int index = dataFile.getActiveSheetIndex();
dataSheet = dataFile.getSheetAt(index);
}
headerColumns = null;
int numMergedRegions = dataSheet.getNumMergedRegions();
mergedRegions = new ArrayList<>(numMergedRegions);
for (int i = 0; i < numMergedRegions; i++) {
mergedRegions.add(dataSheet.getMergedRegion(i));
}
}
@Override
public List<SeekerAimsTO> buildParticipants() throws Exception {
boolean mark = false;
List<Row> participantRows = new ArrayList<Row>();
for (int i = 0; i < sheetParticipants.getLastRowNum(); i++) {
Row row = sheetParticipants.getRow(i);
if (mark) {
if (row != null) {
participantRows.add(row);
}
}
if (row != null && row.getCell(ParticipantCols.NAME.getColumn()) != null) {
String string = row.getCell(ParticipantCols.NAME.getColumn()).toString();
if (string.contains(ParticipantCols.NAME.getHeader())) {
mark = true;
}
if (mark) {
if (row == null
|| (row.getCell(ParticipantCols.NAME.getColumn()) == null)
|| row.getCell(ParticipantCols.NAME.getColumn()).toString().isEmpty()) {
mark = false;
}
}
}
}
ProgramHeaderTO buildProgramDetails = buildProgramDetails();
List<SeekerAimsTO> processRows = processRows(participantRows);
for (SeekerAimsTO seekerAims : processRows) {
seekerAims.setCountry(buildProgramDetails.getCountry());
}
sLogger.info("Participant list:" + participantRows.size());
return processRows;
}
/**
* Forms report in excel file. Sum weight. Sum volume etc.
*
* @param sheet Sheet where write the items
* @param c Container from where take the info
* @param lastRow last row after all items were written method uses lastRow + 2 to write report in
* next 2 rows after all items of the container were written
*/
private void setReport(Container c, int lastRow) {
String[] reportHeadings = {
"Суммарный вес", // 6
"Суммарный объем", // 7
"Остаток вес", // 8
"Остаток объем" // 9
};
double[] values = {
c.getWeight(),
c.getVolume(),
c.getWeightLimit() - c.getWeight(),
c.getVolumeLimit() - c.getVolume()
};
int dataCell = 5;
int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
lastRow++;
Row headings = outputSheet.createRow(lastRow);
lastRow += 2;
Row data = outputSheet.createRow(lastRow);
for (String report : reportHeadings) {
Cell heading = headings.createCell(dataCell);
heading.setCellValue(report);
Cell dataCellValue = data.createCell(dataCell);
dataCellValue.setCellValue(values[valuesIndex]);
dataCell++;
valuesIndex++;
}
}
@Test
public void overrideRowsInTemplate() throws IOException {
XSSFWorkbook template = new XSSFWorkbook();
template.createSheet().createRow(1);
Workbook wb = new SXSSFWorkbook(template);
try {
Sheet sheet = wb.getSheetAt(0);
try {
sheet.createRow(1);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[1] in the range [0,1] that is already written to disk.",
e.getMessage());
}
try {
sheet.createRow(0);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[0] in the range [0,1] that is already written to disk.",
e.getMessage());
}
sheet.createRow(2);
} finally {
wb.close();
template.close();
}
}
/** Reads excel file and matches cells to values */
public Stock readFile(int firstItem, int lastItem)
throws IllegalStateException, InvalidFormatException, IOException {
List<Item> stockList = new ArrayList();
Workbook book = WorkbookFactory.create(this.sourceFile);
Sheet workSheet = book.getSheetAt(sheet);
Item emptyItem = new Item("Системе не удалось разпознать элемент", 0, 0, 0, 0, 0, 0, 0);
for (int n = firstItem - 1; n < lastItem; n++) {
try {
Row row = workSheet.getRow(n);
Item item =
new Item(
row.getCell(cellCodes[0]).toString(),
row.getCell(cellCodes[1]).getNumericCellValue(),
row.getCell(cellCodes[2]).getNumericCellValue(),
row.getCell(cellCodes[3]).getNumericCellValue(),
row.getCell(cellCodes[4]).getNumericCellValue(),
row.getCell(cellCodes[5]).getNumericCellValue(),
row.getCell(cellCodes[6]).getNumericCellValue(),
row.getCell(cellCodes[7]).getNumericCellValue());
stockList.add(item);
} catch (NullPointerException ex) {
stockList.add(emptyItem);
}
}
// pkg.close();
Stock stock = new Stock(stockList);
return stock;
}
public void writeData(Data data, boolean onlyNumbers) {
try {
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fOutputStream = new FileOutputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
@SuppressWarnings("rawtypes")
ArrayList<ArrayList> dataList = data.getData();
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j);
if (onlyNumbers) {
row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
} else {
row.getCell(j).setCellValue((String) dataList.get(i).get(j));
}
}
}
workbook.write(fOutputStream);
workbook.close();
fOutputStream.close();
workbook = null;
file = null;
sheet = null;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
/**
* get the specified excel sheet and put its value string to list.
*
* @param sheetName excel sheet name
* @param ignoreRows first several rows not to read.
* @param ignoreCols first several cols not to read.
* @param readRows specified row count to read.
* @param readColumns specified column count to read.
* @throws RuntimeException
*/
public List<String> excelToList(
String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
FileInputStream fso = null;
List<String> paraList = new ArrayList<String>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
}
readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
xlRow = xlSheet.getRow(i);
readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
if (xlRow != null) {
for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
paraList.add("");
} else {
paraList.add(xlCell.toString());
}
}
}
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
/**
* Compute width of a column based on a subset of the rows and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @param firstRow 0-based index of the first row to consider (inclusive)
* @param lastRow 0-based index of the last row to consider (inclusive)
* @return the width in pixels
*/
public static double getColumnWidth(
Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
AttributedString str;
TextLayout layout;
Workbook wb = sheet.getWorkbook();
DataFormatter formatter = new DataFormatter();
Font defaultFont = wb.getFontAt((short) 0);
str = new AttributedString(String.valueOf(defaultChar));
copyAttributes(defaultFont, str, 0, 1);
layout = new TextLayout(str.getIterator(), fontRenderContext);
int defaultCharWidth = (int) layout.getAdvance();
double width = -1;
for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
Row row = sheet.getRow(rowIdx);
if (row != null) {
Cell cell = row.getCell(column);
if (cell == null) {
continue;
}
double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
width = Math.max(width, cellWidth);
}
}
return width;
}
private org.apache.poi.ss.usermodel.Row getRowForCoordinate(
RowIndex rowIndex, SheetIndex sheetIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex.value());
org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
if (row == null) row = sheet.createRow(rowIndex.value());
return row;
}
@RequestMapping(value = "/excel", method = RequestMethod.GET)
public void excel(HttpServletResponse response) throws Exception {
Workbook wb = new HSSFWorkbook();
// CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
row = sheet.createRow((short) 1);
row.createCell(0).setCellValue("Mã");
row.createCell(1).setCellValue("Tên");
row.createCell(2).setCellValue("Mã Path");
row.createCell(3).setCellValue("Tên path");
row.createCell(4).setCellValue("Cấp danh mục");
int i = 1;
List<Category> listAll = categoryService.listAll();
for (Category print : listAll) {
if (print.getName().length() > 19) {
i++;
row = sheet.createRow((short) i);
row.createCell(0).setCellValue(String.valueOf(print.getId()));
row.createCell(1).setCellValue(String.valueOf(print.getName()));
row.createCell(2).setCellValue(String.valueOf(print.getPath()));
String path = "";
for (String categoryId : print.getPath()) {
Category get = categoryService.get(categoryId);
path += get.getName() + " >>";
}
row.createCell(3).setCellValue(String.valueOf(path));
row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
}
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
wb.write(response.getOutputStream());
}
/**
* @param excelType
* @param inputStream
* @param clazz
* @param processor
* @param listeners
* @return
*/
public <T extends PoiBaseBo> ExcelImportResult importExcelNoValid(
ExcelType excelType,
InputStream inputStream,
Class<T> clazz,
BatchProcessor<T> processor,
List<ExcelImportListener> listeners) {
visitorStart(listeners, clazz);
ValidateMeta validateMeta = getClassValiateMeta(clazz);
Workbook workbook = getTempleteWorkBook(excelType, inputStream);
int sheetAt = PoiAnnotationResolver.getSheetAt(clazz);
Sheet sheet = workbook.getSheetAt(sheetAt);
int rowStart = PoiAnnotationResolver.getRowStart(clazz);
int columnStart = PoiAnnotationResolver.getColumnStart(clazz);
int rowEnd = PoiAnnotationResolver.getRowEnd(clazz);
int columnEnd = PoiAnnotationResolver.getColumnEnd(clazz);
rowEnd = rowEnd == -1 ? sheet.getLastRowNum() : rowEnd;
columnEnd = columnEnd == -1 ? Integer.MAX_VALUE : columnEnd;
ExcelImportResult processValidateResults = new ExcelImportResult(super.getFileRepository());
int recordCount = rowEnd - rowStart + 1;
int realCount = recordCount;
int batchCount = (int) Math.ceil((double) realCount / super.getBatchSize());
if (batchCount == 1) {
super.setBatchSize(realCount);
}
this.setCountDownLatch(new CountDownLatch(batchCount));
List<ValidateResult<T>> batchValidates = new ArrayList<ValidateResult<T>>();
for (int i = 0; i < batchCount; i++) {
int batchStart = (i) * super.getBatchSize() + rowStart;
int batchEnd = (i + 1) * super.getBatchSize() + rowStart - 1;
if (batchEnd - rowStart + 1 > realCount) {
batchEnd = realCount + rowStart - 1;
}
List<T> batchRecord = batchRecord(columnStart, columnEnd, batchStart, batchEnd, sheet, clazz);
List<ValidateResult<T>> batchValidate =
batchValidate(
validateMeta,
columnStart,
columnEnd,
batchStart,
batchEnd,
sheet,
clazz,
batchRecord,
0);
batchValidates.addAll(batchValidate);
int batchEndFlag = (i + 1) * super.getBatchSize();
if (batchEndFlag > recordCount) {
batchEndFlag = recordCount;
}
;
visitorImporting(listeners, (i) * super.getBatchSize() + 1, batchEndFlag, recordCount, clazz);
super.getRecordProcessor().processRecords(batchValidate, processor);
}
processValidateResults.merge(
processValidateResults(excelType, sheet, batchValidates, processor));
visitorEnd(listeners, clazz);
return processValidateResults;
}
private void setDefaults(Sheet worksheet) {
try {
for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
Row row = worksheet.getRow(rowNo);
if (row == null) row = worksheet.createRow(rowNo);
writeFormula(
PRODUCT_COL,
row,
"IF(ISERROR(VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",2,FALSE)),\"\",VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",2,FALSE))");
writeFormula(
OPENING_BALANCE_COL,
row,
"IF(ISERROR(VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",3,FALSE)),\"\",VLOOKUP($C"
+ (rowNo + 1)
+ ",$Q$2:$S$"
+ (savings.size() + 1)
+ ",3,FALSE))");
}
} catch (Exception e) {
logger.error(e.getMessage());
}
}
public void parse() throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Row row;
Sheet sheet = wb.getSheet(sheetName);
int startingRow = 0;
boolean breakNow = false;
for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
if (breakNow) {
break;
}
row = sheet.getRow(i);
if (row == null) {
break;
}
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j).getStringCellValue().isEmpty()
|| row.getCell(j).getStringCellValue() == null) {
breakNow = true;
break;
}
// category.setCategoryName(row.getCell(j).getStringCellValue());
}
}
inp.close();
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
/**
* Called to update the embedded Excel workbook. As the format and structire of the workbook are
* known in advance, all this code attempts to do is write a new value into the first cell on the
* first row of the first worksheet. Prior to executing this method, that cell will contain the
* value 1.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
* classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
* SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
* processing, throwing this exception if that file is invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
*/
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
PackagePart pPart = null;
Iterator<PackagePart> pIter = null;
List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
pIter = embeddedDocs.iterator();
while (pIter.hasNext()) {
pPart = pIter.next();
if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
|| pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
// Get an InputStream from the pacage part and pass that
// to the create method of the WorkbookFactory class. Update
// the resulting Workbook and then stream that out again
// using an OutputStream obtained from the same PackagePart.
workbook = WorkbookFactory.create(pPart.getInputStream());
sheet = workbook.getSheetAt(SHEET_NUM);
row = sheet.getRow(ROW_NUM);
cell = row.getCell(CELL_NUM);
cell.setCellValue(NEW_VALUE);
workbook.write(pPart.getOutputStream());
}
}
// Finally, write the newly modified Word document out to file.
this.doc.write(new FileOutputStream(this.docFile));
}
}