當前位置：首頁 » 編程語言 » 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寫入相關的閱讀推薦