如何使用java在Excel中的多个单元格中写入数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19655813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write data in multiple cells in Excel using java?
提问by Umasri
I wrote a code for writing data in an Excel sheet.In this i have to write the data in multiple cells.But it is showing some Errors.For one cell it is able to change the data.I kept for loopfor changing the data in multiple cells.For this it is showing Error. Can any one tell me that where i did mistake.
我写了一个代码，用于在 Excel 工作表中写入数据。在此我必须在多个单元格中写入数据。但它显示了一些错误。对于一个单元格，它能够更改数据。我保持循环以更改数据在多个单元格中。为此它显示错误。谁能告诉我我在哪里做错了。
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import java.lang.String;
import javax.swing.JOptionPane;
import jxl.Cell;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.formula.functions.Column;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Sele1
{
public static void main(String[] args)
{
// TODO Auto-generated method stub
String FileName = "C:\Users\u304081\Desktop\Java\new.xlsx";
try
{
FileInputStream fileInputStream3 = new FileInputStream(FileName);
File outputsheetfile1 = new File(FileName);
if(outputsheetfile1.exists())
{
System.out.println("File existed");
try
{
XSSFWorkbook ObjWorkBook = new XSSFWorkbook(fileInputStream3);
XSSFSheet DriverTableSheet = ObjWorkBook.getSheetAt(0);
for(int i=1;i<3;i++)
{
XSSFRow row1 = DriverTableSheet.getRow(i);
XSSFCell Cell1 = row1.getCell(0);
System.out.println("Cell1"+ Cell1);
//System.out.println("Cell2"+ Cell2);
String str = "Abc";
Cell1.setCellValue(str);
FileOutputStream out1 = new FileOutputStream (FileName,false);
ObjWorkBook.write(out1);
fileInputStream3.close();
}
}
catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Error I am getting is:
我得到的错误是：
ObjWorkBook.write(out1);
`"poi-bin-3.9-20121203\poi-3.9\poi-ooxml-3.9-20121203.jar has no source attachment"`
回答by Saraf Sissddharth
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Course Pack Resolution Details");
outputFileName = outPut.getAbsolutePath();
int rownum = 0;`enter code here`
for (int i = 0; i < dataList.size(); i++) {
Object[] objArr = dataList.get(i);
HSSFRow row = sheet.createRow(rownum++);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
sheet.autoSizeColumn((short) cellnum);
if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
} else if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
}
}
}
if (outPut.exists()) {
outPut.delete();
}
FileOutputStream out =
new FileOutputStream(outPut);
workbook.write(out);
DataList is ArrayList of Array Object so you can enter as much data as you want init.
DataList 是 Array Object 的 ArrayList，因此您可以在初始化时输入尽可能多的数据。
Example of DataList:
数据列表示例：
dataList.add(new Object[]{"Sr No.", "Cols1", "cols2", "cols3"......."colsn"});
respective data you can insert in list. this example is for .xls format if you want .xlsx then use xssfworkbook.
您可以在列表中插入相应的数据。此示例适用于 .xls 格式，如果您想要 .xlsx 则使用 xssfworkbook。
May be help you.
可能会帮助你。
回答by user1933888
The error you mentioned:
你提到的错误：
ObjWorkBook.write(out1); Here it is showing Error as "poi-bin-3.9-20121203\poi-3.9\poi-ooxml-3.9-20121203.jar has no source attachment"
Does not seem to be anyways related to the problem you mentioned about writing data in multiple cells to excel.
似乎与您提到的有关在多个单元格中写入数据以达到 excel 的问题无关。
You may want to look at this:
你可能想看看这个：
回答by user5830061
=======write data in excel-file in play framework===============
===============================================================================================================================================================================================================================================================================================================================================================
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sample sheet");
Map<String, Object[]> data = new HashMap<String, Object[]>();
data.put("1", new Object[] {"empNo.", "name", "salary"});
data.put("2", new Object[] {1, "John", 1500000d});
data.put("3", new Object[] {2, "Sam", 800000d});
data.put("4", new Object[] {3, "Dean", 700000d});
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset) {
Row row = sheet.createRow(rownum++);
Object [] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
if(obj instanceof Integer)
cell.setCellValue((Integer)obj);
else if(obj instanceof String)
cell.setCellValue((String)obj);
else if(obj instanceof Double)
cell.setCellValue((Double)obj);
}
}
try {
//new excel file created by fileoutput stream object
FileOutputStream out =
new FileOutputStream(new File("/home/jagasan/workspace-playexcelApp/public/ExcelFile3.xlsx"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return ok("file reading is completed");
=======read data from excel and store in database in playframework=======
=======从excel中读取数据并存储在playframework中的数据库中========
public class Application extends Controller {
/*
* public Result index() { return
* ok(index.render("Your new application is ready.")); }
*/
public Result readExcel() throws FileNotFoundException {
try{
InputStream ExcelFileToRead = new FileInputStream("/home/jagasan/workspace-play/excelApp/public/ExcelFile3.xlsx");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Iterator rows = sheet.rowIterator();
boolean flag=false;
while (rows.hasNext())
{
row=(HSSFRow) rows.next();
if(flag==false)
{
flag=true;
continue;
}
Iterator cells = row.cellIterator();
ExcelFile excelfile=new ExcelFile();
int i=0;
while (cells.hasNext())
{
cell=(HSSFCell) cells.next();
if(i==0)
excelfile.setEmpNo((int)cell.getNumericCellValue());
if(i==1)
excelfile.setName(cell.getStringCellValue());
if(i==2)
excelfile.setSalary(cell.getNumericCellValue());
i++;
}
excelfile.save();
}
}
catch(Exception e)
{
e.printStackTrace();
System.out.println("error");
}
return ok("successful");
}
======in build.sbt=======
======在 build.sbt ========
use the dependency jar file means apache API
使用依赖 jar 文件意味着 apache API
libraryDependencies ++= Seq(
javaJdbc,
cache,
javaWs,
"org.apache.poi" % "poi" % "3.8", "org.apache.poi" % "poi-ooxml" % "3.9"
)
