Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook#close()

public static void main( String[] args ) throws IOException {
    OutputStreamWriter osw = new OutputStreamWriter(System.out, Charset.defaultCharset());
    PrintWriter pw = new PrintWriter(osw);
    NPOIFSFileSystem fs = new NPOIFSFileSystem(new File(args[0]));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    try {
        pw.println( "Drawing group:" );
        wb.dumpDrawingGroupRecords(true);

        int i = 1;
        for (Sheet sheet : wb)
        {
            pw.println( "Sheet " + i + "(" + sheet.getSheetName() + "):" );
            ((HSSFSheet) sheet).dumpDrawingRecords(true, pw);
        }
    } finally {
        wb.close();
        fs.close();
    }
}
 

public static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

public static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

@Override
public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
	final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
	try {
		final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

		for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
			addMinistryPerYearToMap(name, map, myWorkBook.getSheetAt(sheetNr));
		}

		myWorkBook.close();
	} catch (final IOException e) {
		LOGGER.warn("Problem loading", e);
	}

	return map;
}
 

@Override
public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(final String name) {
	final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
	try {
		final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

		for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
			final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

			addDataForYearToMap(name, map, mySheet);
		}
		myWorkBook.close();
	} catch (

	final IOException e) {
		LOGGER.warn("Problem loading", e);
	}

	return map;
}
 

private TupleIterator iteratorRawXLS() throws IOException {
	FileInputStream reader = new FileInputStream(file);
	HSSFWorkbook workbook = new HSSFWorkbook(reader);
	HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
	return new SpreadsheetTupleIterator(sheet.iterator()) {
		@Override
		public void close() {
			try {
				workbook.close();
			} catch (IOException e1) {
			}
			try {
				reader.close();
			} catch (IOException e) {
			}
		}			
	};
}
 

public static void main(String[] args) throws Exception {
	File file = new File("e:\\2016年调查表1.xls");
	HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
	HSSFSheet sheet = wb.getSheetAt(2);
	sheet.getRow(5).getCell(3).setCellValue("山东有限公司");
	sheet.getRow(5).getCell(7).setCellValue("3799991911");
	sheet.getRow(8).getCell(3).setCellValue("174");
	sheet.getRow(8).getCell(7).setCellValue("私营股份有限公司");
	wb.write(new FileOutputStream("e:\\2016年调查表2.xls"));
	wb.close();
	System.out.println("success");
}
 

public static void writeToFile(OutputStream fos, InputStream xlsWorkbook, boolean excludeWorkbookRecords, String[] params) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(xlsWorkbook);
    InternalWorkbook internalWorkbook = workbook.getInternalWorkbook();
    DrawingGroupRecord r = (DrawingGroupRecord) internalWorkbook.findFirstRecordBySid(DrawingGroupRecord.sid);

    StringBuilder builder = new StringBuilder();
    builder.append("<workbook>\n");
    String tab = "\t";
    if (!excludeWorkbookRecords && r != null) {
        r.decode();
        List<EscherRecord> escherRecords = r.getEscherRecords();
        for (EscherRecord record : escherRecords) {
            builder.append(record.toXml(tab));
        }
    }
    List<Integer> sheets = getSheetsIndexes(params, workbook);
    for (Integer i : sheets) {
        HSSFPatriarch p = workbook.getSheetAt(i).getDrawingPatriarch();
        if(p != null ) {
            builder.append(tab).append("<sheet").append(i).append(">\n");
            builder.append(p.getBoundAggregate().toXml(tab + "\t"));
            builder.append(tab).append("</sheet").append(i).append(">\n");
        }
    }
    builder.append("</workbook>\n");
    fos.write(builder.toString().getBytes(StringUtil.UTF8));
    fos.close();
    workbook.close();
}
 

public static void writeXLSFile() throws Exception {

		HSSFWorkbook hddfwb = new HSSFWorkbook();
		HSSFSheet sheet = hddfwb.createSheet(sheetName);

		for (int row = 0; row < tableData.size(); row++) {
			HSSFRow hssfrow = sheet.createRow(row);
			rowData = tableData.get(row);
			for (int col = 0; col < rowData.size(); col++) {
				HSSFCell hssfcell = hssfrow.createCell(col);
				hssfcell.setCellValue(rowData.get(col));
			}
		}

		try (OutputStream fileOutputStream = new FileOutputStream(
				excelFileName)) {
			hddfwb.write(fileOutputStream);
			hddfwb.close();
			fileOutputStream.flush();
			fileOutputStream.close();
		} catch (IOException e) {
			String message = String.format("Exception saving XLS file %s\n",
					excelFileName) + e.getMessage();
			logger.info(message);
			// NOTE: throw exceptions with user friendly messages to be rendered
			// by the master app
			throw new Exception(message);
		}
	}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {

    String fileLocation = DatasourceId.ONSWellbeing.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    HSSFWorkbook workbook = new HSSFWorkbook(isr);
    int attributeIndex = 0;

    // Looping through the excell sheets
    for (int sheet = 1; sheet <= 8; sheet = sheet+2){
        Sheet datatypeSheet = workbook.getSheetAt(sheet);
        Row rowTime = datatypeSheet.getRow(5);

        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        // Skipping unrelevant rows
        int ignore = 0;
        while (ignore++ < 7) {
            rowIterator.next();
        }

        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geograghy =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geograghy);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geograghy);

            // Checking if subject is null
            if (subject != null) {

                // Looping through the time values
                for (int timeValuesIndex=2; timeValuesIndex <= 7; timeValuesIndex++ ) {
                    // This is the row number that contains our time values (years) in the dataset
                    String year = rowTime.getCell(timeValuesIndex).toString();
                    year = year.substring(0, year.length() - 3);
                    LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
                    log.info("Time is presented in the dataset as {} and we persist it as {}", year, timestamp);

                    try {
                        Double record = row.getCell(timeValuesIndex).getNumericCellValue();
                        // Here is where we are assigning the values of our .xls file to the attribute fields we
                        // created.
                        Attribute attribute = datasource.getTimedValueAttributes().get(attributeIndex);
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    } catch (IllegalStateException e) {
                        log.warn("Value for subject " + subject.getLabel() + " not found. " +
                                "Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
                        continue;
                    }
                }
            }
        }
        attributeIndex++;
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    // Get the url and pass it to a InputStream for downloading the file
    String fileLocation = DatasourceId.ONSLifeExpectancy.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    // Initialise the array that will store the dataset values
    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    // Initialise the workbook that will be used to interact with the excel dataset
    HSSFWorkbook workbook = new HSSFWorkbook(isr);
    int sheetIndex = 0;

    // Getting the indices of the columns containing the data.
    List<Integer> columnLoop = new ArrayList<Integer>(Arrays.asList(4,8,13));

    // Looping through the excel sheets: HE - male at bith, HE - female at birth, HE - male at 65, HE - females at 65
    // We skip the first (Contents)
    for (int sheet = 1; sheet <= 4; sheet++) {
        Sheet datatypeSheet = workbook.getSheetAt(sheet);

        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();

        // Skipping unrelevant rows
        int ignore = 0;
        while (ignore++ < 4) {
            rowIterator.next();
        }

        Row rowTime = datatypeSheet.getRow(0);
        String year = rowTime.getCell(0).getStringCellValue();
        LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year.substring(year.length() - 4));
        log.info("Time is presented in the dataset as {} and we persist it as {}", year, timestamp);

        Row rowAttribute = datatypeSheet.getRow(3);


        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // The Area Codes is the first column in the excel file
            String geograghy = String.valueOf(row.getCell(0)).trim();

            // Fetch the geometry for our subjects based on the geography code.
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geograghy);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geograghy);

            // Checking if subject is null
            if (subject != null) {

                // Looping through the columns attributes
                for (Integer item : columnLoop) {
                    try {
                        // We are distinguishing our attributes using a combination of column name and sheet name
                        String attributeName = rowAttribute.getCell(item).toString() + " " + datatypeSheet.getSheetName();
                        // Here is where we are assigning the values of our .xls file to the attribute fields we created.
                        Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
                        Double record = row.getCell(item).getNumericCellValue();
                        timedValues.add(new TimedValue(subject, attribute, timestamp, record));
                    } catch (java.lang.IllegalStateException ne) {
                        continue;
                    }
                }
            }
        }
    }
    // Finally we save the values in the database
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    String fileLocation = DatasourceId.ONSNewBusinessSurvival.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    List<TimedValue> timedValues = new ArrayList<TimedValue>();
    HSSFWorkbook workbook = new HSSFWorkbook(isr);

    // Looping through the excell sheets
    for (int sheet = 13; sheet <= 17; sheet++){
        Sheet datatypeSheet = workbook.getSheetAt(sheet);
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        int ignore = 0;
        while (ignore++ < 6) {
            rowIterator.next();
        }
        // Hardcoded year of survey
        String year = null;
        switch (sheet){
            case 13:
                year = "2011";
                break;
            case 14:
                year = "2012";
                break;
            case 15:
                year = "2013";
                break;
            case 16:
                year = "2014";
                break;
            case 17:
                year = "2015";
                break;
        }
        Row rowAttribute = datatypeSheet.getRow(6);
        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geography =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geography);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geography);
            // Checking if subject is null
            if (subject != null) {
                // loop through attribute columns
                for (int i=3; i<=12;i++){
                    String attributeName = String.valueOf(rowAttribute.getCell(i)).trim();
                    LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
                    try {
                        Double record;
                        if (attributeName.contains("per cent")) {
                            record = row.getCell(i).getNumericCellValue() / 100;
                            log.info("Value for " + subject.getLabel()+". Appears as: "+ row.getCell(i).getNumericCellValue()+
                            " Saving as: "+record);
                        } else {
                            record = row.getCell(i).getNumericCellValue();
                        }
                        row.getCell(i).getNumericCellValue();
                        Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    } catch (IllegalStateException | NullPointerException e) {
                        log.warn("Invalid value for subject " + subject.getLabel()+". Skipping");
                        continue;
                    }
                }
            }
        }
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 

@Test
public void sendMailWithExcel() throws IOException {
	String[] headers = {"col1","col2","col3"};
	// 声明一个工作薄
	HSSFWorkbook wb = new HSSFWorkbook();
	// 生成一个表格
	HSSFSheet sheet = wb.createSheet();
	HSSFRow row = sheet.createRow(0);
	for (int i = 0; i < headers.length; i++) {
		HSSFCell cell = row.createCell(i);
		cell.setCellValue(headers[i]);
	}
	int rowIndex = 1;

	for(int j=0; j<3; j++){
		row = sheet.createRow(rowIndex);
		rowIndex++;
		HSSFCell cell1 = row.createCell(0);
		cell1.setCellValue(j);
		cell1 = row.createCell(1);
		cell1.setCellValue(j+1);
		cell1 = row.createCell(2);
		cell1.setCellValue(j+2);
	}
	for (int i = 0; i < headers.length; i++) {
		sheet.autoSizeColumn(i);
	}

	ByteArrayOutputStream os = new ByteArrayOutputStream(1000);
	wb.write(os);
	wb.close();

	InputStreamSource iss = new ByteArrayResource(os.toByteArray());
	os.close();

	mailService.sendAttachmentsMail("[email protected]",
			"attachmentMail subject",
			"I have an attachment",
			iss, "abc1.xlsx");

}
 

public static void outputResumeProcessResult(Map<File, Resume> map, File outFile) throws IOException {
  //工作簿
  HSSFWorkbook excel = new HSSFWorkbook();
  FileOutputStream fileoutputstream = new FileOutputStream(outFile);
  
  try {
    HSSFSheet sheet = excel.createSheet("简历处理结果");
    
    // 写表头
    HSSFRow headerRow = sheet.createRow(0);
    for(int i = 0; i < HEADERS.length; i++) {
      headerRow.createCell(i).setCellValue(HEADERS[i]);
    }
    
    // 写内容
    int rowNum = 1;
    for(Map.Entry<File, Resume> entry : map.entrySet()) {
      Resume resume = entry.getValue();
      HSSFRow row = sheet.createRow(rowNum);
      row.createCell(0).setCellValue(rowNum);                    // 序号
      row.createCell(1).setCellValue(resume.getName());          // 姓名
      row.createCell(2).setCellValue(resume.getJob());          // 应聘职位
      row.createCell(3).setCellValue(resume.getSource());        // 简历来源
      row.createCell(4).setCellValue(resume.getSchool());        // 毕业院校
      row.createCell(5).setCellValue(resume.getEducation());    // 学历
      row.createCell(6).setCellValue(resume.getAge());           // 年龄
      row.createCell(7).setCellValue(resume.getWorkDuration()); // 工作经验
      row.createCell(8).setCellValue(resume.getCompany());      // 现工作单位
      row.createCell(9).setCellValue(resume.getPhone());         // 电话
      row.createCell(10).setCellValue(resume.getMail());          // 邮箱
      row.createCell(11).setCellValue(resume.getSex());           // 性别
      row.createCell(12).setCellValue(resume.getBirthday());      // 生日
      row.createCell(13).setCellValue(resume.getCity());          // 居住城市
      row.createCell(14).setCellValue(resume.getAddress());       // 地址
      rowNum++;
    }
    excel.write(fileoutputstream);
  } finally {
    fileoutputstream.close();
    excel.close();
  }
}