@Override
public void parse(InputStream in, ICallback callback) {
Workbook wb = null;
try {
callback.begin();
wb = WorkbookFactory.create(in);
int sheetNumber = wb.getNumberOfSheets();
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
int line = this.startLine;
Row row = null;
while ((row = sheet.getRow(line)) != null) {
Object[] data = this.readLineData(row);
if (data != null) callback.visit(data, line);
line++;
}
}
callback.end();
} catch (Exception e) {
Log.logError(e.getMessage());
} finally {
try {
in.close();
} catch (Exception ex) {
}
}
}
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;
}
/**
* 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;
}
@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());
}
@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();
}
}
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();
}
}
@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");
}
private void ReadDataToArray(Workbook dataWb) {
Connection con = DbConnection.getConnection();
String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);";
for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) {
if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) {
try {
preparedStatement = con.prepareStatement(DataSheetInsertQuery);
preparedStatement.setString(
1,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue()));
preparedStatement.setDate(
2,
new Date(
(dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime()));
preparedStatement.setString(
3,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
DbConnection.closePreparedStatement(preparedStatement);
DbConnection.closeConnection(con);
System.out.println("All the data in the sheet is analysed and updated to memory");
}
public void removerCliente(String cpf) {
int count = 1;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
while (count < ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
Row row = wb.getSheetAt(0).getRow(count);
wb.getSheetAt(0).removeRow(row);
achou = true;
} else {
count++;
}
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
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;
}
// ����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";
}
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));
}
}
/**
* 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));
}
}
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();
}
public void rebuildFormula() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
rebuildFormula(book.getSheetAt(i));
}
addSumBottom();
}
/**
* public Result getDataBinding(String postcode) throws PostcodeNotFoundException {
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class);
* if(response == null){ throw new PostcodeNotFoundException("Postcode " +
* postcode.toUpperCase()+" cannot be found."); } } catch (JsonParseException e) {
* System.out.println("JsonParseException: "+ e.toString()); } catch (JsonMappingException e) {
* System.out.println("JsonMappingException: "+ e.toString()); } catch (IOException e) {
* System.out.println("IOException: "+ e.toString()); } countNum++; return response; }
*
* <p>public Result getDataBindingFirstTry(String postcode) {
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class); }catch
* (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
* (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
* (IOException e) { // System.out.println("IOException: "+ e.toString()); } return response; }
*
* <p>public Result getDataBindingReTry(String postcode, String bindingName){
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+bindingName+".json"), Result.class); }catch
* (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
* (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
* (IOException e) { System.out.println("IOException: "+ e.toString()); } countNum++; return
* response;
*
* <p>}
*
* <p>public boolean saveJsonFileByPostcode(String postcode, String url) throws
* PostcodeNotFoundException{
*
* <p>// System.setProperty("http.proxySet", "true"); //
* System.setProperty("http.proxyHost","proxy.abdn.ac.uk"); //
* System.setProperty("http.proxyPort", "8080"); URL website = null; ReadableByteChannel rbc =
* null; FileOutputStream fos = null; try { website = new URL(url); rbc =
* Channels.newChannel(website.openStream()); String dir =
* this.getClass().getClassLoader().getResource("").getPath(); // System.out.println("dir:
* "+dir+""+ POSTCODE_FOLDER+File.separator+postcode); File directory = new File(dir+""+
* POSTCODE_FOLDER); if(!directory.exists()){ directory.mkdirs(); } fos = new FileOutputStream(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"));
* fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE); fos.flush(); fos.close(); return true;
* }catch (MalformedURLException e) { e.printStackTrace(); }catch(FileNotFoundException e){ //
* e.printStackTrace(); throw new PostcodeNotFoundException("Postcode "+postcode.toUpperCase()+"
* cannot be found."); }catch (IOException e) { e.printStackTrace(); } return false; }
*
* <p>public String getURLByPostcode(String postcode){
*
* <p>String url = "http://uk-postcodes.com/postcode/"; url += postcode; url += ".json"; return
* url; }
*/
public void readExcel() {
String path = this.getClass().getClassLoader().getResource("").getPath();
File file = new File(path + File.separator + POSTCODE_TABLE);
OPCPackage opcPackage = null;
try {
opcPackage = OPCPackage.open(file);
} catch (InvalidFormatException e) {
e.printStackTrace();
}
// Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
Sheet sheet = null;
if (POSTCODE_TABLE.toLowerCase().endsWith("xlsx")) {
try {
workbook = new XSSFWorkbook(opcPackage);
} catch (IOException e) {
e.printStackTrace();
}
}
// Get the number of sheets in the xlsx file
int numberOfSheets = workbook.getNumberOfSheets();
if (numberOfSheets > 0) {
// System.out.println("read the first sheet from the workbook...");
sheet = workbook.getSheetAt(0);
}
read(sheet);
}
/** 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 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;
}
@Override
public void createCellStyles(Workbook wb) {
if (wb == null) {
return;
}
CreationHelper createHelper = wb.getCreationHelper();
// Headers
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.WHITE.index);
headerStyle.setFont(font);
cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);
// Date and Time
CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
cellStyleMap.put(STYLE_DATE_CELL, dateStyle);
CellStyle timeStyle = wb.createCellStyle();
timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
}
/**
* 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());
}
}
/**
* 基类基于子类提供的相关参数数据, 生成JXLS报表
*
* @see #exportXlsForGrid(List, Sort, GroupPropertyFilter) 此方法中基于参数组装好相关的data数据后，调用此方法生成Excel响应
* @param dataMap
*/
protected void exportExcel(
String templateFileName, String exportFileName, Map<String, Object> dataMap) {
// 日期格式定义
dataMap.put("dateFormatter", new SimpleDateFormat(DateUtils.DEFAULT_DATE_FORMAT));
dataMap.put("timeFormatter", new SimpleDateFormat(DateUtils.DEFAULT_TIME_FORMAT));
HttpServletResponse response = ServletActionContext.getResponse();
InputStream fis = null;
OutputStream fos = null;
try {
Resource resource = new ClassPathResource("/template/xls/" + templateFileName);
logger.debug("Open template file inputstream: {}", resource.getURL());
fis = resource.getInputStream();
XLSTransformer transformer = new XLSTransformer();
// generate the excel workbook according to the template and
// parameters
Workbook workbook = transformer.transformXLS(fis, dataMap);
String filename = exportFileName;
filename = new String(filename.getBytes("GBK"), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
fos = response.getOutputStream();
// output the generated excel file
workbook.write(fos);
} catch (Exception e) {
throw new WebException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(fis);
IOUtils.closeQuietly(fos);
}
}
/**
* 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;
}
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);
}
}
public Sheet getSheet(String fileName) throws IOException, InvalidFormatException {
InputStream inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
return sheet;
}
public static void main(String args[]) {
try {
String fileName = "C:/Users/NITHA/Desktop/Task.xlsx";
// Create the input stream from the xlsx/xls file
FileInputStream fis = new FileInputStream(fileName);
// Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if (fileName.toLowerCase().endsWith("xlsx")) {
workbook = new XSSFWorkbook(fis);
} else if (fileName.toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(fis);
}
// Get the nth sheet from the workbook
Sheet sheet = workbook.getSheet(HEADER_VALUES);
retrieveSheetInformation(sheet);
// close file input stream
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@RequestMapping(value = "exportExcel")
public String exportExcel(HttpServletRequest request, HttpServletResponse response)
throws Exception {
String operator = request.getParameter("filter_LIKES_operator");
String time = request.getParameter("filter_LIKES_time");
StringBuilder builder = new StringBuilder("from OptLog where 1=1");
if (StringUtils.isNotBlank(operator)) {
builder.append(" and operator like '%").append(operator).append("%'");
request.setAttribute("filter_LIKES_operator", operator);
}
if (StringUtils.isNotBlank(time)) {
builder.append(" and time like '%").append(time).append("%'");
request.setAttribute("filter_LIKES_time", time);
}
builder.append(" order by id desc");
List<OptLog> data = optLogManager.findOptLogOrderby(builder.toString());
// 生成Excel文件.
Workbook wb = new ExcelExporter().export("操作日志", data);
// 输出Excel文件.
// HttpServletResponse response = Struts2Utils.getResponse();
response.setContentType(ServletUtils.EXCEL_TYPE);
ServletUtils.setFileDownloadHeader(response, "操作日志.xls");
wb.write(response.getOutputStream());
response.getOutputStream().flush();
return null;
}
/**
* @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 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;
}
public <T> List<T> map() throws Throwable {
InputStream inputStream = null;
List<T> items = new LinkedList<>();
try {
Iterator<Row> rowIterator;
inputStream = new FileInputStream(excelFile);
Workbook workbook = createWorkbook(inputStream);
int numberOfSheets = workbook.getNumberOfSheets();
for (int index = 0; index < numberOfSheets; index++) {
Sheet sheet = workbook.getSheetAt(index);
rowIterator = sheet.iterator();
Map<String, Integer> nameIndexMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
readExcelHeader(row, nameIndexMap);
} else {
items.add((T) readExcelContent(row, nameIndexMap));
}
}
}
} finally {
if (inputStream != null) {
inputStream.close();
}
}
return items;
}
