POI Kullanımı

Maven

Şu satırı dahil ederiz

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.1.2</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.2</version>
</dependency>

Yazma

Her şey XSSWorkbook sınıfı etrafında dönüyor.

Örnek

Şöyle yaparız

ByteArrayOutputStream bos = new ByteArrayOutputStream();

try (Workbook workbook = new XSSFWorkbook()) {
  ...
} catch (Exception e) {
  ...
} finally {
  bos.close();
}
return bos.toByteArray();

Yazarken Font ve fontların kullanılacağı CellStyle nesnelerini oluşturmak gerekir. Şöyle yaparız

//setting up the basic styles for the workbook
Font boldFont = getBoldFont(workbook);
Font genericFont = getGenericFont(workbook);
CellStyle headerStyle = getLeftAlignedCellStyle(workbook, boldFont);
CellStyle currencyStyle = setCurrencyCellStyle(workbook);
CellStyle centerAlignedStyle = getCenterAlignedCellStyle(workbook);
CellStyle genericStyle = getLeftAlignedCellStyle(workbook, genericFont);

Daha sonra bir Sheet oluşturulur. Şöyle yaparız

String sheetName = ...
Sheet sheet = workbook.createSheet(sheetName);

Daha sonra bir başlık oluşturulur. Şöyle yaparız

int tempRowNo = 0;

//set spreadsheet titles
Row mainRow = sheet.createRow(tempRowNo++);

for (int i = 0; i < columnTitles.length; i++) {
  Cell columnTitleCell = mainRow.createCell(i);
  columnTitleCell.setCellStyle(headerStyle);
  columnTitleCell.setCellValue(columnTitles[i]);
}

Daha sonra veriyi sütunlar halinde yazarız. Şöyle yaparız

//looping the dataset
for (T record : data) {
  
  Row mainRow = sheet.createRow(tempRowNo++);
  Cell compositeNewCell = mainRow.createCell(cellIndex);
  cell.setCellValue(...);
  Hyperlink link = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
  link.setAddress(...);
  cell.setHyperlink(link);
  ...
}

Tabi bu işleri yaparken bir sürü yardımcı metod gerekiyor. Bunları hep kodlamak lazım

Örnek

Elimizde şöyle bir kod olsun

public class ExcelGenerator {
  private List < Student > studentList = ...;
  private XSSFWorkbook workbook = new XSSFWorkbook();
  private XSSFSheet sheet;

  private void writeHeader() {
    sheet = workbook.createSheet("Student");
    Row row = sheet.createRow(0);
    CellStyle style = workbook.createCellStyle();
    XSSFFont font = workbook.createFont();
    font.setBold(true);
    font.setFontHeight(16);
    style.setFont(font);
    createCell(row, 0, "ID", style);
    createCell(row, 1, "Student Name", style);
    createCell(row, 2, "Email", style);
    createCell(row, 3, "Mobile No.", style);
  }
  private void createCell(Row row, int columnCount, Object valueOfCell,
    CellStyle style) {
    sheet.autoSizeColumn(columnCount);
    Cell cell = row.createCell(columnCount);
    if (valueOfCell instanceof Integer) {
      cell.setCellValue((Integer) valueOfCell);
    } else if (valueOfCell instanceof Long) {
      cell.setCellValue((Long) valueOfCell);
    } else if (valueOfCell instanceof String) {
      cell.setCellValue((String) valueOfCell);
    } else {
      cell.setCellValue((Boolean) valueOfCell);
    }
    cell.setCellStyle(style);
  }
  private void write() {
    int rowCount = 1;
    CellStyle style = workbook.createCellStyle();
    XSSFFont font = workbook.createFont();
    font.setFontHeight(14);
    style.setFont(font);
    for (Student record: studentList) {
      Row row = sheet.createRow(rowCount++);
      int columnCount = 0;
      createCell(row, columnCount++, record.getId(), style);
      createCell(row, columnCount++, record.getStudentName(), style);
      createCell(row, columnCount++, record.getEmail(), style);
      createCell(row, columnCount++, record.getMobileNo(), style);
    }
  }
}

Şöyle yaparız. Burada Excep direkt servlet cevabına yazılıyor

public class ExcelGenerator {
  
  public void generateExcelFile(HttpServletResponse response) throws IOException {
    writeHeader();
    write();
    ServletOutputStream outputStream = response.getOutputStream();
    workbook.write(outputStream);
    workbook.close();
    outputStream.close();
  }
}

Dosya İndirme

Örnek - SpringBoot

Şöyle yaparız

import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

@Controller
public class UserController {

  @Autowired
  private UserService userService;

  @RequestMapping(method = RequestMethod.POST, value = "/download-users")
  public ResponseEntity downloadUsersExcel() {
    try {
      final byte[] data = userService.getUserXlsData();
      HttpHeaders header = new HttpHeaders();
      header.setContentType(MediaType.parseMediaType(
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"));
      header.set(HttpHeaders.CONTENT_DISPOSITION, "inline; filename= users.xlsx");
      header.setContentLength(data.length);
      return new ResponseEntity<>(data, header, HttpStatus.OK);
    } catch (Exception e) {
      return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
    }
  }
}

POI Excel WorkbookFactory Sınıfı

Giriş
Şu satırı dahil ederiz.

import org.apache.poi.hssf.usermodel.WorkBookFactory;

HSSFWorkbook veya XSSFWorkbook nesnesini yaratır. Her iki nesne de Workbook arayüzünden kalıtır.

create metodu
Örnek - File
Şöyle yaparız.

Workbook workbook = WorkbookFactory.create(new File(fileName));

create metodu - InputStream
Örnek
Şöyle yaparız

WorkbookFactory.create(new ByteArrayInputStream(...));

Örnek - FileInputStream
Şöyle yaparız.

InputStream inputStream = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(inputStream);

Örnek - FileInputStream
Şöyle yaparız

File file = ...
Workbook workbook = new XSSFWorkbook(new FileInputStream(file));

POI Excel XSSFSheetXMLHandler Arayüzü

constructor - Styles + Comments + SharedStrings + XSSFSheetXMLHandler.SheetContentsHandler + DataFormatter dataFormatter, boolean formulasNotResults
Örnek
Şöyle yaparız

import org.apache.poi.openxml4j.opc.OPCPackage
import org.apache.poi.ooxml.util.SAXHelper
import org.apache.poi.xssf.eventusermodel.XSSFReader
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable
import org.apache.poi.hssf.usermodel.HSSFDataFormatter
import org.xml.sax.InputSource

class MyHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    ...
}

def pkg = OPCPackage.open(request.getBody(InputStream.class))
def xssfReader = new XSSFReader(pkg)
def sheetParser = SAXHelper.newXMLReader()

def handler = new XSSFSheetXMLHandler(xssfReader.getStylesTable(), null,
  new ReadOnlySharedStringsTable(pkg), MyHandler, new HSSFDataFormatter(), false)
sheetParser.setContentHandler(handler)
sheetParser.parse(new InputSource(xssfReader.getSheetsData().next()))

POI Excel OPCPackage Sınıfı

Giriş
Şu satırı dahil ederiz.

import org.apache.poi.openxml4j.opc.OPCPackage

XSSFReader nesnesi yaratmak için kullanılır.

open metodu - InputStream
Örnek - XSSFWorkbook 

Şöyle yaparız

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

File file = new File("./inspection.xlsx");

//OPCPackage pkg = OPCPackage.open(file);
OPCPackage pkg = OPCPackage.open(new FileInputStream(file));
XSSFWorkbook wb = new XSSFWorkbook(pkg);
    
//XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
//Workbook wb = WorkbookFactory.create(new FileInputStream(file));

//wb -> sheets -> rows -> cols
int finding = 445;

DataFormatter formatter = new DataFormatter();
boolean write = false;
for(Sheet sheet : wb) {
  for(Row row : sheet) {
    if(row.getCell(0)!=null && !formatter.formatCellValue(row.getCell(0)).equals("")) {
      Cell cell = row.getCell(0);
      String text = formatter.formatCellValue(cell);
      if('0'<=text.charAt(0) && text.charAt(0)<='9') {
        int id = Integer.parseInt(text);
        if (id == finding) {
          System.out.println(sheet.getSheetName());
          System.out.println(sheet.getRow(row.getRowNum()).getCell(1));
          Cell cellCurrent = row.getCell(2);
          if (cellCurrent == null) {
            cellCurrent = row.createCell(2);
          }
          cellCurrent.setCellValue("X");
          write = true;
        }
      }
    }
  }
}
if (write) {
  FileOutputStream outputStream = new FileOutputStream(file);
  wb.write(outputStream);
  outputStream.close();
  wb.close();
}

Örnek - XSSReader

Şöyle yaparız

def pkg = OPCPackage.open(request.getBody(InputStream.class))
def xssfReader = new XSSFReader(pkg)