Parse excel with java

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Jexcel is an Excel file parser that allows you to easily create and parse Excel files in Java

License

NotNaturalSelection/jexcel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Jexcel is a .xls and .xlsx file parser that allows you to easily create and parse Excel files in Java.

Jexcel requires Java 1.8+ and Apache org.apache.poi.poi-ooxml of version 4.0.0+.

Download jexcel-X.X.jar with org.apache.poi.poi-ooxml of version 4.0.0+ and add them to your classpath.

If you have simple data class that has no fields except of String , Date ( LocalDateTime etc.) and primitives you can just use org.notnaturalselection.jexcel.parsing.StandardParser . Otherwise, extend org.notnaturalselection.jexcel.parsing.AbstractParser and write your own implementation of parseRow(Row row) method.

Implement the org.notnaturalselection.jexcel.parsing.Header interface. Then describe all the fields you want to parse.

public enum ExampleHeader implements Header < stringField(0, "stringColumn", "", false), integerField(1, "intColumn", null, true), dateField(2, "dateColumn", null, true); private final int columnNumber; private final String columnName; private final Object defaultValue; private final boolean isRequired; //constructor and getters from interface //. >

Note: column name should be equal to the name of the variable it represents. Otherwise org.notnaturalselection.jexcel.exceptions.FieldMappingException will be recorded or thrown.

public class ExampleObject < private String stringColumn; private Integer intColumn; private Date dateColumn; >

StandardParserExampleObject, ExampleHeader> parser = new StandardParser<>( ExampleObject::new, Arrays.asList(ExampleHeader.values()) );

ParseResultExampleObject> result = parser.parse(new File("file.xlsx")); //or ParseResultExampleObject> result = parser.parse(new FileInputStream("file.xlsx")); //if your file is protected by password ParseResultExampleObject> result = parser.parse(new File("file.xlsx"), password); //or ParseResultExampleObject> result = parser.parse(new FileInputStream("file.xlsx"), password);

MapString, ListExampleObject>> map = result.getResult();

Also org.notnaturalselection.jexcel.parsing.dto.ParseResult contains warnings that appeared in process of parsing.

ListString> warnings = result.getWarnings();

###Warnings Parser accepts org.notnaturalselection.jexcel.parsing.WarningPolicy .

• WarningPolicy.STRONG . In case of error exception will be thrown.
• WarningPolicy.WEAK . In case of error parser records a warning that appears in ParseResult .

###Standard parser instance Constructor arguments:

• WarningPolicy wp — see the «Warnings» block
• int verticalOffset — number of rows that will be skipped from the first row
• int horizontalOffset — number of columns that will be skipped from the first column
• Supplier supplier — constructor or method to create a new instance of result object
• Iterable headers — headers that will be parsed

List of available constructors:

• public StandardParser(WarningPolicy wp, int verticalOffset, int horizontalOffset, Supplier supplier, Iterable headers)
• public StandardParser(int verticalOffset, int horizontalOffset, Supplier supplier, Iterable headers)
• public StandardParser(WarningPolicy wp, Supplier supplier, Iterable headers)
• public StandardParser(Supplier supplier, Iterable headers)

About

Jexcel is an Excel file parser that allows you to easily create and parse Excel files in Java

Источник

Работа с таблицей Excel из Java

Собственно возникла проблема — обработать данные из таблицы и на их основе получить другую таблицу.

1. Макрос — единственной проблемой является VBA, на изучение которого времени нет совершенно, да и не нравится его синтаксис
2. Приложение на C# тут вроде все хорошо, но к машине на которой будет выполняться данное приложение сразу предъявляется много дополнительных требований:
   • .NET Framework
   • Установленный офис
   • установленная основная сборка взаимодействия (PIA) для приложения Office

3. связка Java и библиотека Apache POI—на этом способе я и хочу остановиться подробнее

1. POI 3.5 beta 5, and Office Open XML Support (2009-02-19)—идет работа над поддержкой формата Office 2007
2. POI 3.2-FINAL Released (2008-10-19) — последний стабильный релиз

Я расскажу о работе с версией 3.2
Основным классом для работы с таблицей Excel является класс HSSFWorkbook пакета org.apache.poi.hssf.usermodel, представляющий книгу Excel.

Для чтения книги из файла можно применить следующий код:

public static HSSFWorkbook readWorkbook(String filename) < try < POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); return wb; >catch (Exception e) < return null; >>

Метод возвращает объект класса HSSFWorkbook если все удачно и null в другом случае.

Для сохранения изменений можно применить следующий метод:

public static void writeWorkbook(HSSFWorkbook wb, String fileName) < try < FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); >catch (Exception e) < //Обработка ошибки >>

Метод записывает книгу wb в файл fileName

• По имени
  HSSFSheet sheet= wb.getSheet(«Лист 3»)
• По номеру (нумерация начинается с 0)
  HSSFSheet sheet= wb.getSheet(0)
• Создание нового листа
  HSSFSheet sheet= wb.createSheet([«имя листа»])

• По индексу (индексация начинается с 0)
  HSSFRow row = sheet.getRow(index)
• Через итератор

Iterator rowIter = sheet.rowIterator(); while (rowIter.hasNext())

• По индексу ячейки (индексация начинается с 0)
  HSSFCell cell = row.getCell(0);
• Через итератор

Iterator cellIter = row.cellIterator(); while (cellIter.hasNext())

• Логическое значение
  boolean b = cell.getBooleanCellValue();
  cell.setCellValue(b);
• Дата
  Date date = cell.getDateCellValue();
  cell.setCellValue(date);
• Числовое значение
  double d = cell.getNumericCellValue();
  cell.setCellValue(d);
• Строковое значение
  String str = cell.getRichStringCellValue().getString();
  cell.setCellValue(new HSSFRichTextString(str));
• Формула
  String formula = cell.getCellFormula();
  cell.setCellFormula(formula);

Этих знаний достаточно чтобы обрабатывать простые таблицы.
Библиотека также предоставляет богатые возможности по форматированию ячеек, по их слиянию, заморозке и т.д.
Подробное описание функций можно найти на их сайте.
Данный способ прежде всего ценен тем, что не требует установки самого офиса и пакета PIA.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Annotated Excel parsing library to simplify parsing excel sheet in JAVA

License

nvenky/excel-parser

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

HSSF — Horrible Spreadsheet Format – not anymore. With few annotations, excel parsing can be done in one line.

We had a requirement in our current project to parse multiple excel sheets and store the information to database. I hope most of the projects involving excel sheet parsing would be doing the same. We built a extensible framework to parse multiple sheets and populate JAVA objects with annotations.

This JAR is currently available in Sonatype maven repository.

dependency> groupId>org.javafunkgroupId> artifactId>excel-parserartifactId> version>1.0version> dependency>

compile 'org.javafunk:excel-parser:1.0' 

Thanks to tobyclemson for publishing this to Maven repository.

Student Information Example

Consider we have an excel sheet with student information.

While parsing this excel sheet, we need to populate one “Section” object and multiple “Student” objects related to a Section. You can see that Student information is available in multiple rows whereas the Section details (Year, Section) is available in column B.

Step 1: Annotate Domain Classes

First we will see the steps to annotate Section object:

@ExcelObject(parseType = ParseType.COLUMN, start = 2, end = 2) public class Section < @ExcelField(position = 2) private String year; @ExcelField(position = 3) private String section; @MappedExcelObject private List Student> students; >

You can find three different annotation in this class.

• ExcelObject : This annotation tells the parser about the parse type (Row or Column), number of objects to create (start, end). Based on the above annotation, Section value should be parsed Columnwise and information can be found in Column 2 (“B”) of the Excelsheet.
• ExcelField : This annotation tells the parser to fetch “year” information from Row 2 and “section” information from Row 3.
• MappedExcelObject : Apart from Simple datatypes like “Double”,”String”, we might also try to populate complex java objects while parsing. In this case, each section has a list of student information to be parsed from excel sheet. This annotation will help the parser in identifying such fields.

Then, annotate the Student class:

@ExcelObject(parseType = ParseType.ROW, start = 6, end = 8) public class Student < @ExcelField(position = 2) private Long roleNumber; @ExcelField(position = 3) private String name; @ExcelField(position = 4) private Date dateOfBirth; @ExcelField(position = 5) private String fatherName; @ExcelField(position = 6) private String motherName; @ExcelField(position = 7) private String address; @ExcelField(position = 8) private Double totalScore; >

• ExcelObject : As shown above, this annotation tells parser to parse Rows 6 to 8 (create 3 student objects). NOTE: Optional field “zeroIfNull” , if set to true, will populate Zero to all number fields (Double,Long,Integer) by default if the data is not available in DB.
• ExcelField : Student class has 7 values to be parsed and stored in the database. This is denoted in the domain class as annotation.
• MappedExcelObject : Student class does not have any complex object, hence this annoation is not used in this domain class.

Step 2: Invoke Sheet Parser

Once the annotation is done, you have just invoke the parser with the Sheet and the Root class you want to populate.

//Get the sheet using POI API. String sheetName = "Sheet1"; SheetParser parser = new SheetParser(); InputStream inputStream = getClass().getClassLoader().getResourceAsStream("Student Profile.xls"); Sheet sheet = new HSSFWorkbook(inputStream).getSheet(sheetName); //Invoke the Sheet parser. List entityList = parser.createEntity(sheet, sheetName, Section.class);

Thats all it requires. Parser would populate all the fields based on the annotation for you.

• JDK 8
• Run «gradle idea» to setup the project
• Install Lombok plugin
• Enable «Enable annotation processing» as this project uses Lombok library. [Compiler > Annotation Processors > Enable annotation processing: checked ]

Источник