Saya ingin membaca dan menulis file Excel dari Jawa dengan 3 kolom dan N baris, mencetak satu string di setiap sel. Siapa pun dapat memberikan saya snippet kode sederhana untuk ini? Apakah saya perlu menggunakan external lib atau Jawa tidak memiliki built-in mendukung untuk itu?
Saya ingin melakukan hal-hal berikut:
for(i=0; i <rows; i++)
//read [i,col1] ,[i,col2], [i,col3]
for(i=0; i<rows; i++)
//write [i,col1], [i,col2], [i,col3]
Mencoba Apache POI HSSF. Berikut ini's contoh tentang cara membaca file excel:
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if(row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp > cols) cols = tmp;
}
}
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell((short)c);
if(cell != null) {
// Your code here
}
}
}
}
} catch(Exception ioe) {
ioe.printStackTrace();
}
Pada halaman dokumentasi anda juga memiliki contoh-contoh dari bagaimana untuk menulis ke file excel.
Apache POI dapat melakukan ini untuk anda. Secara khusus HSSF modul. The panduan cepat adalah yang paling berguna. Berikut ini's bagaimana untuk melakukan apa yang anda inginkan - secara khusus membuat lembar dan menulis itu.
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
Pertama tambahkan semua file jar di proyek kelas jalan:
Kode untuk menulis di file excel:
public static void main(String[] args) {
//Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Employee Data");
//This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
data.put("2", new Object[]{1, "Amit", "Shukla"});
data.put("3", new Object[]{2, "Lokesh", "Gupta"});
data.put("4", new Object[]{3, "John", "Adwards"});
data.put("5", new Object[]{4, "Brian", "Schultz"});
//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset)
{
//create a row of excelsheet
Row row = sheet.createRow(rownum++);
//get object array of prerticuler key
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr)
{
Cell cell = row.createCell(cellnum++);
if (obj instanceof String)
{
cell.setCellValue((String) obj);
}
else if (obj instanceof Integer)
{
cell.setCellValue((Integer) obj);
}
}
}
try
{
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));
workbook.write(out);
out.close();
System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
}
catch (Exception e)
{
e.printStackTrace();
}
}
Kode untuk membaca dari file excel
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Ada yang baru mudah dan alat yang sangat keren (10x untuk Kfir): xcelite
Menulis:
public class User {
@Column (name="Firstname")
private String firstName;
@Column (name="Lastname")
private String lastName;
@Column
private long id;
@Column
private Date birthDate;
}
Xcelite xcelite = new Xcelite();
XceliteSheet sheet = xcelite.createSheet("users");
SheetWriter<User> writer = sheet.getBeanWriter(User.class);
List<User> users = new ArrayList<User>();
// ...fill up users
writer.write(users);
xcelite.write(new File("users_doc.xlsx"));
Baca:
Xcelite xcelite = new Xcelite(new File("users_doc.xlsx"));
XceliteSheet sheet = xcelite.getSheet("users");
SheetReader<User> reader = sheet.getBeanReader(User.class);
Collection<User> users = reader.read();
Untuk membaca file xlsx kita dapat menggunakan Apache POI libs Coba ini:
public static void readXLSXFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
XSSFWorkbook test = new XSSFWorkbook();
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row=(XSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell=(XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
}
String path="C:\\Book2.xlsx";
try {
File f = new File( path );
Workbook wb = WorkbookFactory.create(f);
Sheet mySheet = wb.getSheetAt(0);
Iterator<Row> rowIter = mySheet.rowIterator();
for ( Iterator<Row> rowIterator = mySheet.rowIterator() ;rowIterator.hasNext(); )
{
for ( Iterator<Cell> cellIterator = ((Row)rowIterator.next()).cellIterator() ; cellIterator.hasNext() ; )
{
System.out.println ( ( (Cell)cellIterator.next() ).toString() );
}
System.out.println( " **************************************************************** ");
}
} catch ( Exception e )
{
System.out.println( "exception" );
e.printStackTrace();
}
dan pastikan untuk menambahkan guci poi dan poi-ooxml (org.apache.poi) untuk proyek anda
Untuk membaca data dari .xlsx workbook kita perlu menggunakan XSSFworkbook kelas.
XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);
XSSFSheet lembar = xlsxBook.getSheetAt(0);
dll.
Kita perlu menggunakan Apache poi 3.9 @ http://poi.apache.org/
Untuk info rinci dengan contoh berkunjung : http://java-recent.blogspot.in
Yakin , anda akan menemukan kode di bawah ini berguna dan mudah untuk membaca dan menulis. Ini adalah util
kelas yang dapat anda gunakan dalam metode utama anda dan kemudian anda baik untuk menggunakan semua metode di bawah ini.
public class ExcelUtils {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;
File fileName = new File("C:\\Users\\satekuma\\Pro\\Fund.xlsx");
public void setExcelFile(File Path, String SheetName) throws Exception
try {
FileInputStream ExcelFile = new FileInputStream(Path);
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
} catch (Exception e) {
throw (e);
}
}
public static String getCellData(int RowNum, int ColNum) throws Exception {
try {
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Cell.getStringCellValue();
return CellData;
} catch (Exception e) {
return "";
}
}
public static void setCellData(String Result, int RowNum, int ColNum, File Path) throws Exception {
try {
Row = ExcelWSheet.createRow(RowNum - 1);
Cell = Row.createCell(ColNum - 1);
Cell.setCellValue(Result);
FileOutputStream fileOut = new FileOutputStream(Path);
ExcelWBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
throw (e);
}
}
}
menggunakan spring apache poi repo
if (fileName.endsWith(".xls")) {
File myFile = new File("file location" + fileName);
FileInputStream fis = new FileInputStream(myFile);
org.apache.poi.ss.usermodel.Workbook workbook = null;
try {
workbook = WorkbookFactory.create(fis);
} catch (InvalidFormatException e) {
e.printStackTrace();
}
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
}
System.out.print(" - ");
}
System.out.println();
}
}
Anda perlu Apache POI perpustakaan dan kode ini di bawah ini akan membantu anda
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
//*************************************************************
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//*************************************************************
public class AdvUse {
private static Workbook wb ;
private static Sheet sh ;
private static FileInputStream fis ;
private static FileOutputStream fos ;
private static Row row ;
private static Cell cell ;
private static String ExcelPath ;
//*************************************************************
public static void setEcxelFile(String ExcelPath, String SheetName) throws Exception {
try {
File f= new File(ExcelPath);
if(!f.exists()){
f.createNewFile();
System.out.println("File not Found so created");
}
fis = new FileInputStream("./testData.xlsx");
wb = WorkbookFactory.create(fis);
sh = wb.getSheet("SheetName");
if(sh == null){
sh = wb.getSheet(SheetName);
}
}catch(Exception e)
{System.out.println(e.getMessage());
}
}
//*************************************************************
public static void setCellData(String text , int rowno , int colno){
try{
row = sh.getRow(rowno);
if(row == null){
row = sh.createRow(rowno);
}
cell = row.getCell(colno);
if(cell!=null){
cell.setCellValue(text);
}
else{
cell = row.createCell(colno);
cell.setCellValue(text);
}
fos = new FileOutputStream(ExcelPath);
wb.write(fos);
fos.flush();
fos.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
}
//*************************************************************
public static String getCellData(int rowno , int colno){
try{
cell = sh.getRow(rowno).getCell(colno);
String CellData = null ;
switch(cell.getCellType()){
case STRING :
CellData = cell.getStringCellValue();
break ;
case NUMERIC :
CellData = Double.toString(cell.getNumericCellValue());
if(CellData.contains(".o")){
CellData = CellData.substring(0,CellData.length()-2);
}
break ;
case BLANK :
CellData = ""; break ;
}
return CellData;
}catch(Exception e){return ""; }
}
//*************************************************************
public static int getLastRow(){
return sh.getLastRowNum();
}
Saya diedit paling sebagai salah satu sedikit cuz itu tidak't hitung kosong kolom atau baris yang tidak benar, jadi di sini adalah kode saya diuji dan sekarang bisa mendapatkan setiap sel di setiap bagian dari file excel. juga sekarang u bisa memiliki kosong antara kolom diisi kolom dan akan membacanya
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(Dir));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
int cblacks=0;
// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i <= 10 || i <= rows; i++) {
row = sheet.getRow(i);
if(row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp >= cols) cols = tmp;else{rows++;cblacks++;}
}
cols++;
}
cols=cols+cblacks;
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell(c);
if(cell != null) {
System.out.print(cell+"\n");//Your Code here
}
}
}
}} catch(Exception ioe) {
ioe.printStackTrace();}
Jika jumlah kolom yang varing anda dapat menggunakan ini
package com.org.tests;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelSimpleTest
{
String path;
public FileInputStream fis = null;
private XSSFWorkbook workbook = null;
private XSSFSheet sheet = null;
private XSSFRow row =null;
private XSSFCell cell = null;
public ExcelSimpleTest() throws IOException
{
path = System.getProperty("user.dir")+"\\resources\\Book1.xlsx";
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
}
public void ExelWorks()
{
int index = workbook.getSheetIndex("Sheet1");
sheet = workbook.getSheetAt(index);
int rownumber=sheet.getLastRowNum()+1;
for (int i=1; i<rownumber; i++ )
{
row = sheet.getRow(i);
int colnumber = row.getLastCellNum();
for (int j=0; j<colnumber; j++ )
{
cell = row.getCell(j);
System.out.println(cell.getStringCellValue());
}
}
}
public static void main(String[] args) throws IOException
{
ExcelSimpleTest excelwork = new ExcelSimpleTest();
excelwork.ExelWorks();
}
}
Sesuai mavendependency dapat ditemukan di sini
Silakan gunakan Apache POI libs dan mencoba ini.
public class TakingDataFromExcel {
public static void main(String[] args) {
try
{
FileInputStream x = new FileInputStream(new File("/Users/rajesh/Documents/rajesh.xls"));
//Create Workbook instance holding reference to .xlsx file
Workbook workbook = new HSSFWorkbook(x);
//Get first/desired sheet from the workbook
Sheet sheet = workbook.getSheetAt(0);
//Iterate through each rows one by one
for (Iterator<Row> iterator = sheet.iterator(); iterator.hasNext();) {
Row row = (Row) iterator.next();
for (Iterator<Cell> iterator2 = row.iterator(); iterator2
.hasNext();) {
Cell cell = (Cell) iterator2.next();
System.out.println(cell.getStringCellValue());
}
}
x.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
Cara lain untuk membaca/menulis file Excel adalah dengan menggunakan Angin. Ini memberikan pengalaman API untuk proses Excel dan CSV file.
try (Stream<Row> rowStream = Windmill.parse(FileSource.of(new FileInputStream("myFile.xlsx")))) {
rowStream
// skip the header row that contains the column names
.skip(1)
.forEach(row -> {
System.out.println(
"row n°" + row.rowIndex()
+ " column 'User login' value : " + row.cell("User login").asString()
+ " column n°3 number value : " + row.cell(2).asDouble().value() // index is zero-based
);
});
}
Windmill
.export(Arrays.asList(bean1, bean2, bean3))
.withHeaderMapping(
new ExportHeaderMapping<Bean>()
.add("Name", Bean::getName)
.add("User login", bean -> bean.getUser().getLogin())
)
.asExcel()
.writeTo(new FileOutputStream("Export.xlsx"));
Anda tidak bisa membaca & write file yang sama secara paralel(Read-write lock). Tapi, kita bisa melakukan operasi paralel pada data sementara(yaitu Input/output stream). Menulis data ke file hanya setelah penutupan input stream. Berikut langkah-langkah yang harus diikuti.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XLSXReaderWriter {
public static void main(String[] args) {
try {
File excel = new File("D://raju.xlsx");
FileInputStream fis = new FileInputStream(excel);
XSSFWorkbook book = new XSSFWorkbook(fis);
XSSFSheet sheet = book.getSheetAt(0);
Iterator<Row> itr = sheet.iterator();
// Iterating over Excel file in Java
while (itr.hasNext()) {
Row row = itr.next();
// Iterating over each column of Excel file
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
default:
}
}
System.out.println("");
}
// writing data into XLSX file
Map<String, Object[]> newData = new HashMap<String, Object[]>();
newData.put("1", new Object[] { 1d, "Raju", "75K", "dev",
"SGD" });
newData.put("2", new Object[] { 2d, "Ramesh", "58K", "test",
"USD" });
newData.put("3", new Object[] { 3d, "Ravi", "90K", "PMO",
"INR" });
Set<String> newRows = newData.keySet();
int rownum = sheet.getLastRowNum();
for (String key : newRows) {
Row row = sheet.createRow(rownum++);
Object[] objArr = newData.get(key);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
}
}
}
// open an OutputStream to save written data into Excel file
FileOutputStream os = new FileOutputStream(excel);
book.write(os);
System.out.println("Writing on Excel file Finished ...");
// Close workbook, OutputStream and Excel file to prevent leak
os.close();
book.close();
fis.close();
} catch (FileNotFoundException fe) {
fe.printStackTrace();
} catch (IOException ie) {
ie.printStackTrace();
}
}
}
Ini akan menulis sebuah JTable ke tab file terpisah yang dapat dengan mudah diimpor ke Excel. Ini bekerja.
Jika anda menyimpan lembar kerja Excel sebagai sebuah dokumen XML anda juga bisa membangun XML file EXCEL dengan kode. Saya telah melakukan ini dengan kata-kata sehingga anda tidak harus menggunakan paket pihak ketiga.
Ini bisa kode memiliki JTable diambil dan kemudian hanya menulis tab terpisah untuk setiap file teks dan kemudian impor ke Excel. Saya harap ini membantu.
Kode:
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JTable;
import javax.swing.table.TableModel;
public class excel {
String columnNames[] = { "Column 1", "Column 2", "Column 3" };
// Create some data
String dataValues[][] =
{
{ "12", "234", "67" },
{ "-123", "43", "853" },
{ "93", "89.2", "109" },
{ "279", "9033", "3092" }
};
JTable table;
excel() {
table = new JTable( dataValues, columnNames );
}
public void toExcel(JTable table, File file){
try{
TableModel model = table.getModel();
FileWriter excel = new FileWriter(file);
for(int i = 0; i < model.getColumnCount(); i++){
excel.write(model.getColumnName(i) + "\t");
}
excel.write("\n");
for(int i=0; i< model.getRowCount(); i++) {
for(int j=0; j < model.getColumnCount(); j++) {
excel.write(model.getValueAt(i,j).toString()+"\t");
}
excel.write("\n");
}
excel.close();
}catch(IOException e){ System.out.println(e); }
}
public static void main(String[] o) {
excel cv = new excel();
cv.toExcel(cv.table,new File("C:\\Users\\itpr13266\\Desktop\\cs.tbv"));
}
}
Jika anda perlu untuk melakukan sesuatu yang lebih dengan dokumen office di Jawa, pergi untuk POI seperti yang disebutkan.
Untuk membaca/menulis sebuah dokumen excel seperti yang kau minta, anda bisa menggunakan format CSV (juga disebutkan):
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Scanner;
public class CsvWriter {
public static void main(String args[]) throws IOException {
String fileName = "test.xls";
PrintWriter out = new PrintWriter(new FileWriter(fileName));
out.println("a,b,c,d");
out.println("e,f,g,h");
out.println("i,j,k,l");
out.close();
BufferedReader in = new BufferedReader(new FileReader(fileName));
String line = null;
while ((line = in.readLine()) != null) {
Scanner scanner = new Scanner(line);
String sep = "";
while (scanner.hasNext()) {
System.out.println(sep + scanner.next());
sep = ",";
}
}
in.close();
}
}