I am using Apache poi to extract Mysql data to an Excel file. The code is running correctly but when I am trying to open the excel file it is showing error.
package com.telkomsel.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.telkomsel.configuirator.Configurator;
import com.telkomsel.dbconnection.DBConnection;
import com.telkomsel.service.TelkomselEntities;
public class TelkomselExcel {
DBConnection db = new DBConnection();
static Configurator configurator = null;
Connection conn = null;
static Statement statement = null;
static ResultSet resultSet = null;
public static HashMap<Integer, TelkomselEntities> getTelkomselData(Statement statement) {
configurator = new Configurator();
String Query = configurator.getProperty("sql_query1");
HashMap<Integer, TelkomselEntities> all = null;
TelkomselEntities smsModel = null;
try {
all = new HashMap<Integer, TelkomselEntities>();
resultSet = statement.executeQuery(Query);
while (resultSet.next()) {
int hour = resultSet.getInt("hour(timestamp)");
String count = resultSet.getString("count(1)");
smsModel = new TelkomselEntities(hour, count, count, count);
all.put(hour, smsModel);
}
smsModel = new TelkomselEntities();
FileInputStream fis = new FileInputStream(new File("Tracker.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet worksheet = workbook.getSheetAt(0);
XSSFRow row = null;
XSSFCell cell;
int i = 1;
for (Integer l : all.keySet()) {
TelkomselEntities us = all.get(l);
row = worksheet.createRow(i);
cell = row.createCell(2);
cell.setCellValue(us.getHour());
cell = row.createCell(3);
cell.setCellValue(us.getCharge_Count());
i++;
}
fis.close();
FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),true);
System.out.println("SUCCESS");
workbook.write(output_file);
workbook.close();
output_file.flush();
output_file.close();
} catch (Exception e) {
System.out.println(e);
}
return all;
}
}
I think file output stream is creating problem as it converts data into byte codes. i tried every thing but doesn’t work. my excel file is not working
Advertisement
Answer
As you supposed, the problem hides inside the line:
FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),true);
When creating a new XSSFWorkbook Java Object from an existing excel (which you want to update), that XSSFWorkbook is initially created based on your excel file content, then it is totally independent from it.The proof of this is that all changes to the XSSFWorkbook Java Object ARE NOT going to affect the original excel file at all. Apache Poi works that way!
This is the reason why once you’re done editing your XSSFWorkbook you have to save it as a new excel file (using a FileOutputStream) overriding the original one (in a sense, you’re now updating your excel file with all your changes).
But as the docs says, you’re telling FileOutputStream not to override the original excel file with the new and updated one but to append the second to the first one, upsi dupsi! You’re creating a single file which contains both all the bytes of the original old file and all the bytes of the new updated one!
To solve the problem, just use instead:
FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"),false);
or
FileOutputStream output_file = new FileOutputStream(new File("Tracker.xlsx"));
and you’re done!
Edit: learn Apache Poi before using Apache Poi
It seems that you’re using FileOutputStream wrong because you don’t know how Apache Poi works and how to use it. You might want to study a little bit about it before using it, the web is full of examples and tutorials! Here they are some examples provided by Apache Poi itself, you might want to have a look at them.
As I said before, the XSSFWorkbook is initialized with all the content of your original excel file. So if you start filling your XSSFSheet from the second line (that’s what you’re actually doing with your code) you are literally asking to your XSSFWorkbook to override existing data with new one.
You have to improve your code, searching for already existing data in rows and cells and not overriding it if you don’t want to.
Rows and cells of each XSSFSheet of your XSSFWorkbook are numbered using 0-based indexes (that’s the reason why your code, which starts filling rows from index 1, is filling rows starting from the second one).
With the method XSSFSheet#getRow(int rownum) you can retreive any row from the current XSSFSheet indicating its 0-based index. If this method returns null, then the row you’re asking for has never been used and you have to create it using the method XSSFSheet#createRow(int rownum). If it doesn’t, then the row you’re asking for has already been used and contains some data in some of its cells.
With the method XSSFRow#getCell(int cellnum) you can retrieve any cell from the current XSSFRow indicating its 0-based index. If this method returns null, then the cell you’re asking for has never been used and you have to create it using the method XSSFRow#createCell(int cellnum, CellType celltype). If it doesn’t, then the cell you’re asking for has already been used and contains some data in it.
You can retrieve the CellType of an existing XSSFCell with the method XSSFCell#getCellType().
You can retreive the content of an existing XSSFCell (on the basis of its CellType) using such methods as XSSFCell#getStringCellValue(), XSSFCell#getNumericCellValue() or XSSFCell#getBooleanCellValue().
Other useful methods are XSSFSheet#getLastRowNum() and XSSFRow#getLastCellNum(). The first one returns the index of the last already used row inside your sheet, the second one returns the index of the first not used cell inside your row.
Here it is an example for you (filling 42 rows of your sheet after the last existing one):
public static void main(String[] args) throws EncryptedDocumentException, FileNotFoundException, IOException {
// Step 1: load your excel file as a Workbook
String excelFilePath = "D:\Desktop\textExcel.xlsx";
XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(excelFilePath));
// Step 2: modify your Workbook as you prefer
XSSFSheet sheet = workbook.getSheetAt(0);
int firstUnusedRowIndex = sheet.getLastRowNum() + 1;
for (int rowIndex = firstUnusedRowIndex ; rowIndex < firstUnusedRowIndex + 42 ; rowIndex++) {
sheet.createRow(rowIndex).createCell(0, CellType.STRING).setCellValue("New Row n°" + (rowIndex - firstUnusedRowIndex + 1));
}
// Step 3: update the original excel file
FileOutputStream outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
workbook.close();
outputStream.close();
}