当前位置：首页 » 编程语言 » javaexcel写入

javaexcel写入

发布时间: 2021-03-20 16:59:44

① java如何向指定的EXCEL单元格中写入数据

我们项目里用的供你参考，没写全，你可以自己网络下

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
* 导出excel
* @ reportParams 导出excel列名标示
* @param list导出excel值
* @param headersexcel头
* @param reportName·excel的sheet名
* @param response
* @param startRow从哪一行开始放list值
* @param startCol 从哪一列开始放list值
* @return
*/
public boolean report(ReportBean rb, List list, XlsHeaderBean[] headers, String reportName, HttpServletResponse response, int startRow, int startCol, HttpServletRequest request){
WritableWorkbook wwb = null;
OutputStream os;
boolean flag = true;
Date date = new Date();
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String strDate = format.format(date);
try {
// 用Workbook类的工厂方法创建工作薄(Workbook)对象
response.setContentType("application/x-msdownload");
String sheetName = "report";
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "")
.replaceAll("[(]", "");
// 这里解释一下
// attachment; 这个代表要下载的，如果去掉就编程直接打开了
// filename是文件名，另存为或者下载时，为默认的文件名
response.addHeader("Content-Disposition", "attachment; filename="
+ new String(sheetName.getBytes("UTF-8"), "ISO-8859-1")+ strDate
+ ".xls");
os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
} catch (IOException e) {
e.printStackTrace();
return flag = false;
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法两个参数，1名称，2位置
WritableSheet ws = wwb.createSheet(reportName, 0);
// 下面开始添加单元格
// 导出excel
try {
Label labelC = null;
for(int j = 0; j < headers.length; j++){
if(headers[j].isUnion()){
ws.mergeCells(headers[j].getCol(), headers[j].getRow(), headers[j].getCol()+headers[j].getColLength(), headers[j].getRow()+headers[j].getRowLength());
}
labelC = new Label(headers[j].getCol(), headers[j].getRow(), headers[j].getValue());
ws.addCell(labelC);
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
String value = null;
for (int i = 0; i < list.size(); i++) {
Map values = (Map)list.get(i);
for (int j = 0; j < rb.getReportParams().length; j++) {
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC;
if(values.get(rb.getReportParams()[j]) == null){
value = "";
} else {
if(values.get(rb.getReportParams()[j]) instanceof java.util.Date){
value = format.format(values.get(rb.getReportParams()[j]));
} else if(values.get(rb.getReportParams()[j]) instanceof java.math.BigDecimal){
value = values.get(rb.getReportParams()[j]).toString();
} else {
value = values.get(rb.getReportParams()[j]).toString();
}
}
labelC = new Label(j+startCol, i + startRow, value);
try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
return flag = false;
} catch (WriteException e) {
e.printStackTrace();
return flag = false;
}
}
}
if(rb.isHasImg()){
String rootPath = this.getServletContext().getRealPath("savefiles");
String imgPath = rb.getImgPath();
if(rootPath != null && rootPath.compareTo("") != 0 && imgPath != null && imgPath.compareTo("") !=0){
String[] strs = imgPath.split("\\/");
String imgName = strs[strs.length-1];
File file = new File(rootPath + File.separator + imgName);
if(file.exists()){
WritableImage wi = new WritableImage(0, startRow + list.size() + 2,12,20, file);
ws.addImage(wi);
}
}
}
try {
// 从内存中写入文件中
wwb.write();
wwb.close();
return flag;
} catch (IOException e) {
e.printStackTrace();
return flag = false;
} catch (WriteException e) {
e.printStackTrace();
return flag = false;
}
}
return flag;
}

② Java 向Excel指定单元格写入数据

Labelxuexiao=newLabel(1,1,"aaaaa");

sheet.addCell(xuexiao);//addCell

book.write();//write下有红线

参数，或参考类型都不正确。。。。。。。。。。。

WritableCell

WritableSheet

等

③ java将画面中输入的内容，写入到指定的Excel文件的指定的行列中（具体实例）

刚解答了一个类似问题，采用的是jxl.jar包。贴代码说话：
public void createExcel(String path) {
try {
// 在path路径下建立一个excel文件
WritableWorkbook wbook = Workbook.createWorkbook(new File(path));
// 创建一个工作表第一个工作区
WritableSheet wsheet = wbook.createSheet("数据清单", 0);
// 设置excel里的字体
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.NO_BOLD, false);
// 给标题规定字体的格式
WritableCellFormat titleFormat = new WritableCellFormat(wf);

String[] title = { "账号", "密码"};
// 设置表头
for (int i = 0; i < title.length; i++) {
// 一列列的打印表头按照我们规定的格式
Label excelTitle = new Label(i, 0, title[i], titleFormat);

// 把标头加到我们的工作区
wsheet.addCell(excelTitle);
}

Label account = new Label(0, 1, "银弹小哥");
Label password = new Label(1, 1, "111111");

// 把值加到工作表中
wsheet.addCell(account);
wsheet.addCell(password);

// 写入文件
wbook.write();
wbook.close();
System.out.println("创建成功!");
} catch (Exception e) {
// TODO: handle exception
}
}
说明：以上代码中你可以将画面输入的内容写到”银弹小哥“，”111111“那样的位置上。
解释下new Label(0, 1, "银弹小哥");第一个0表示0列，1表示第一行。一般行列都是从0，0开始。这样你就可以把内容写到指定的行列中。

④ JAVA怎么向Excel中写入批量数据

public static void drawExcel(HSSFWorkbook wb, String sheetName, String title, int n, List<?> exlList, int[] index){
List<Object[]> exList =(List<Object[]>)exlList;
int len = exList.get(0).length;
// 创建一个sheet表单
HSSFSheet sheet = wb.createSheet(sheetName);
Region region = null;
//样式
HSSFCellStyle cellStyle1 = setStyleBorder(wb);
HSSFCellStyle cellStyle2 = setStyleFontBorder(wb);
// 创建标题行
HSSFRow row = sheet.createRow(0);
row.setHeight((short)500);
// 创建单元格
HSSFCell cell = null;
if(title != null && !"".equals(title)){
region = new Region(0, (short)0, 0, (short)(len- 1));
sheet.addMergedRegion(region);

cell = row.createCell(0);
// 标题写入单元格
cell.setCellValue(title);
cell.setCellStyle(setStyleFontSize(wb, 18));
}else{
n = n - 1;
}

NumberFormat formatter = NumberFormat.getNumberInstance();
formatter.setMaximumFractionDigits(8);
//合计信息
Double[] sum = new Double[len];
Object[] s = null;
for(int i = 0; i < exList.size(); i++){
s = exList.get(i);
row = sheet.createRow(i + n);
// 创建数据行
for(int j = 0; j < s.length; j++){
cell = row.createCell(j);
/***
* jobin create
*/
if(s[j] instanceof Integer || s[j] instanceof Float){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(!StringUtils.isNotEmpty(s[j])){
cell.setCellStyle(cellStyle1);
continue;
}
String[] rs = null;
int l = 3000;
if(i == 0 && s[j].toString().indexOf(",") > 0){
rs = s[j].toString().split(",");
cell.setCellValue(rs[0]);
l = Integer.parseInt(rs[1]);
}else{
cell.setCellValue(s[j].toString());
}
if(i == 0){
cell.setCellStyle(cellStyle2);
//设置列宽
// if(j == 0){
sheet.setColumnWidth(j, l);
// }else if(j == s.length -1){
// sheet.setColumnWidth(j, 5000);
// }else{
// sheet.setColumnWidth(j, 3000);
// }
}else{
cell.setCellStyle(cellStyle1);
//合计统计
if(index != null){
for(int in : index){
if(in == j){
if(sum[in] == null) sum[in] = 0.0;
sum[in] += Double.parseDouble(s[j].toString());
}
}
}
}
}
}
//合计信息
if(index != null){
region = new Region(exList.size() + 1, (short)0, exList.size() + 1, (short)(index[0]-1));
sheet.addMergedRegion(region);
row = sheet.createRow(exList.size() + n);
for(int i = 0; i < sum.length; i++){
cell = row.createCell(i);
cell.setCellStyle(cellStyle2);
if(i == 0){
cell.setCellValue("合计");
}else if(sum[i] != null){
cell.setCellValue(formatter.format(sum[i]).replace(",", ""));
}
}
}

}
核心代码，我项目上使用的

⑤ 如何用java向指定的Excel中写入数据

jxl，可以实现，开源的jar包很多，目前我使用的是jxl
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String id =req.getParameter("id");
String fileName =req.getParameter("fileName");
File f = new File(req.getSession().getServletContext().getRealPath("/printTemplateExcel/"+fileName));
WritableWorkbook wwb=null;
WritableSheet wws=null;
FileOutputStream out =null;
Label label = null;
Workbook wb=null;
try {
FesOrders fesOrders =new FesOrders();
fesOrders.setId(Long.parseLong(id));
List<FesOrders> fesOrdersList = fesOrderService.findList(fesOrders, (SysUser)req.getSession().getAttribute(SESSION_USER));
for(FesOrders a : fesOrdersList){
System.out.println(a.getId());
}
WritableFont headerFont =new WritableFont(WritableFont.createFont("新宋体"), 10, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat dataFormat=new WritableCellFormat (headerFont);
dataFormat.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//设置边框
WritableCellFormat wf = new WritableCellFormat(dataFormat);
wb = Workbook.getWorkbook(f);
out = new FileOutputStream(new File(req.getSession().getServletContext().getRealPath("/printExcel/"+fileName)));
wwb = Workbook.createWorkbook(out, wb);
wws = wwb.getSheet("出货单");
String date=sdf.format(new Date());
for(int i=0;i<wws.getRows();i++){
for(int j=0;j<wws.getColumns();j++){
Cell cell = wws.getCell(j, i);
if(cell.getContents().trim().indexOf("${year}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${year}", date.subSequence(0, 4)));
}
if(cell.getContents().trim().indexOf("${mouth}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${mouth}", date.substring(5, 7)));
}
if(cell.getContents().trim().indexOf("${day}")>0){
label = (Label)cell;
label.setString(cell.getContents().replace("${day}", date.substring(8, 10)));
}
}
}
System.out.println(fesOrdersList.size());
for(int i=0;i<fesOrdersList.size();i++){
FesOrders fesOrder= fesOrdersList.get(i);
List<FesSendOrders> fesSendOrders=fesOrder.getFesSendOrderses();
wws.addCell(new Label(0,i+2,i+1+"",wf));
wws.addCell(new Label(1,i+2,fesOrder.getSysUser().getCnName(),wf));
wws.addCell(new Label(2,i+2,fesOrder.getInnerSendNo(),wf));
wws.addCell(new Label(3,i+2,fesSendOrders.get(0).getFesTransportModes().getName(),wf));
if(fesSendOrders.get(0).getOpWeight()!=null){
wws.addCell(new Label(4,i+2,fesSendOrders.get(0).getOpWeight().toString(),wf));
}
wws.addCell(new Label(5,i+2,"",wf));
wws.addCell(new Label(6,i+2,fesSendOrders.get(0).getFesAddresseesBySenderId().getAddress(),wf));
wws.addCell(new Label(7,i+2,"",wf));
wws.addCell(new Label(8,i+2,fesOrder.getOrderNo(),wf));
wws.addCell(new Label(9,i+2,"",wf));
}
wwb.write();
wwb.close();
out.flush();
out.close();
download(req,res,fileName);
这个是我写的简单的导出excel

⑥ java 如何将字符串写入一个已有的excel表格中

操作，用POI这个类库比较多。下面是示例代码
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import java.io.*;

public class Sample3_1{
public static void main(String[] args){
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet();

Row row1 = sheet.createRow(1);
Row row2 = sheet.createRow(2);

Cell cell1_0 = row1.createCell(0);
Cell cell1_1 = row1.createCell(1);
Cell cell1_2 = row1.createCell(2);

Cell cell2_0 = row2.createCell(0);
Cell cell2_1 = row2.createCell(1);
Cell cell2_2 = row2.createCell(2);

cell1_0.setCellValue(10);
cell1_1.setCellValue(-8.5);
cell1_2.setCellValue(3.14);

cell2_0.setCellValue("Hello");
cell2_1.setCellValue("表形式");
cell2_2.setCellValue("3.14");

FileOutputStream out = null;
try{
out = new FileOutputStream("sample3_1.xls");
wb.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
}catch(IOException e){
System.out.println(e.toString());
}
}
}
}

⑦ 怎么用java将一个excel里面数据读出并写入另一个excel

需要对Excel中的数据进行读取操作。

⑧ 如何用java把数据写入到excel

添加Spire.Xls.jar依赖，可以创建Excel,或者对现有Excel文档进行处理。

1.写入数据到指定单元格

//CreateaWorkbookinstance
Workbookwb=newWorkbook();
//Getthefirstworksheet
Worksheetsheet=wb.getWorksheets().get(0);
//Writetextinthespecificcell
sheet.getCellRange(1,1).setText("HelloWorld");
//Savethefile
wb.saveToFile("HelloWorld.xlsx",ExcelVersion.Version2016)

2. 将数组导入Excel

//CreateaWorkbookinstance
Workbookwb=newWorkbook();
//Getthefirstworksheet
Worksheetsheet=wb.getWorksheets().get(0);
//Insertanarraytothefirstcolumn()
String[]stringArray=newString[]{"Apple","Pear","Grape","Banana","Peach"}
sheet.insertArray(stringArray,1,1,true);
//Savethefile
wb.saveToFile("InsertArray.xlsx",ExcelVersion.Version2016);

⑨ 如何用java向指定的EXCEL单元格中写入数据

我们项目里用的供你参考，没写全，你可以自己网络下

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
* 导出excel
* @param reportParams 导出excel列名标示
* @param list导出excel值
* @param headersexcel头
* @param reportName·excel的sheet名
* @param response
* @param startRow从哪一行开始放list值
* @param startCol 从哪一列开始放list值
* @return
*/
public boolean report(ReportBean rb, List list, XlsHeaderBean[] headers, String reportName, HttpServletResponse response, int startRow, int startCol, HttpServletRequest request){
WritableWorkbook wwb = null;
OutputStream os;
boolean flag = true;
Date date = new Date();
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String strDate = format.format(date);
try {
// 用Workbook类的工厂方法创建工作薄(Workbook)对象
response.setContentType("application/x-msdownload");
String sheetName = "report";
sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "")
.replaceAll("[(]", "");
// 这里解释一下
// attachment; 这个代表要下载的，如果去掉就编程直接打开了
// filename是文件名，另存为或者下载时，为默认的文件名
response.addHeader("Content-Disposition", "attachment; filename="
+ new String(sheetName.getBytes("UTF-8"), "ISO-8859-1")+ strDate
+ ".xls");
os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
} catch (IOException e) {
e.printStackTrace();
return flag = false;
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法两个参数，1名称，2位置
WritableSheet ws = wwb.createSheet(reportName, 0);
// 下面开始添加单元格
// 导出excel
try {
Label labelC = null;
for(int j = 0; j < headers.length; j++){
if(headers[j].isUnion()){
ws.mergeCells(headers[j].getCol(), headers[j].getRow(), headers[j].getCol()+headers[j].getColLength(), headers[j].getRow()+headers[j].getRowLength());
}
labelC = new Label(headers[j].getCol(), headers[j].getRow(), headers[j].getValue());
ws.addCell(labelC);
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
String value = null;
for (int i = 0; i < list.size(); i++) {
Map values = (Map)list.get(i);
for (int j = 0; j < rb.getReportParams().length; j++) {
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC;
if(values.get(rb.getReportParams()[j]) == null){
value = "";
} else {
if(values.get(rb.getReportParams()[j]) instanceof java.util.Date){
value = format.format(values.get(rb.getReportParams()[j]));
} else if(values.get(rb.getReportParams()[j]) instanceof java.math.BigDecimal){
value = values.get(rb.getReportParams()[j]).toString();
} else {
value = values.get(rb.getReportParams()[j]).toString();
}
}
labelC = new Label(j+startCol, i + startRow, value);
try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
return flag = false;
} catch (WriteException e) {
e.printStackTrace();
return flag = false;
}
}
}
if(rb.isHasImg()){
String rootPath = this.getServletContext().getRealPath("savefiles");
String imgPath = rb.getImgPath();
if(rootPath != null && rootPath.compareTo("") != 0 && imgPath != null && imgPath.compareTo("") !=0){
String[] strs = imgPath.split("\\/");
String imgName = strs[strs.length-1];
File file = new File(rootPath + File.separator + imgName);
if(file.exists()){
WritableImage wi = new WritableImage(0, startRow + list.size() + 2,12,20, file);
ws.addImage(wi);
}
}
}
try {
// 从内存中写入文件中
wwb.write();
wwb.close();
return flag;
} catch (IOException e) {
e.printStackTrace();
return flag = false;
} catch (WriteException e) {
e.printStackTrace();
return flag = false;
}
}
return flag;
}

⑩ 做一个功能，java创建一个excel文件并写入数据，求完整代码

这个不就是java io完全可以实现输出参数环下就行了
setContenttype 改成EXcel

阅读全文

javaexcel写入

与javaexcel写入相关的阅读推荐