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);
    }
  }
}