當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

java excel转word表格_java利用poi生成/读取excel表格、生成word

發布時間：2024/9/19 编程问答 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 java excel转word表格_java利用poi生成/读取excel表格、生成word 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

1.引入jar包依賴

org.apache.poi

poi

3.9

org.apache.poi

poi-ooxml

3.8

View Code

2.編寫代碼測試

1 packagetestweb;2

3 importjava.io.File;4 importjava.io.FileNotFoundException;5 importjava.io.FileOutputStream;6 importjava.io.IOException;7 importjava.io.OutputStream;8 importjava.util.LinkedList;9 importjava.util.List;10

11 importorg.apache.poi.hssf.usermodel.HSSFCell;12 importorg.apache.poi.hssf.usermodel.HSSFCellStyle;13 importorg.apache.poi.hssf.usermodel.HSSFFont;14 importorg.apache.poi.hssf.usermodel.HSSFRichTextString;15 importorg.apache.poi.hssf.usermodel.HSSFRow;16 importorg.apache.poi.hssf.usermodel.HSSFSheet;17 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;18 importorg.apache.poi.hssf.util.HSSFColor;19

21 public classTestExcel {22

23 public static voidmain(String[] args) {24 List l=new LinkedList<>();25 l.add("zs");26 l.add("ls");27 l.add("we");28 l.add("mz");29 String[] headers=new String[]{"tou1","tou2","tou3","tou4"};30 try{31 OutputStream o= new FileOutputStream(new File("C:/Users/yanan/Desktop/yanantest.xls"));32 exportDataExcel("nihao",headers,l,o);33 } catch(FileNotFoundException e) {34 e.printStackTrace();35 }36 }37

38 protected static void exportDataExcel(String title,String[] headers,ListmapList,OutputStream out){39 //聲明一個工作簿

40 HSSFWorkbook workbook = newHSSFWorkbook();41 //生成一個表格

42 HSSFSheet sheet =workbook.createSheet(title);43 //設置表格默認列寬度字符

44 sheet.setDefaultColumnWidth(20);45 //生成一個樣式，用來設置標題樣式

46 HSSFCellStyle style =workbook.createCellStyle();47 //設置這些樣式

48 style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);49 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);50 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);51 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);52 style.setBorderRight(HSSFCellStyle.BORDER_THIN);53 style.setBorderTop(HSSFCellStyle.BORDER_THIN);54 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);55 //生成一個字體

56 HSSFFont font =workbook.createFont();57 font.setColor(HSSFColor.VIOLET.index);58 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);59 //把字體應用到當前的樣式

60 style.setFont(font);61 //生成并設置另一個樣式,用于設置內容樣式

62 HSSFCellStyle style2 =workbook.createCellStyle();63 style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);64 style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);65 style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);66 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);67 style2.setBorderRight(HSSFCellStyle.BORDER_THIN);68 style2.setBorderTop(HSSFCellStyle.BORDER_THIN);69 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);70 style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);71 //生成另一個字體

72 HSSFFont font2 =workbook.createFont();73 font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);74 //把字體應用到當前的樣式

75 style2.setFont(font2);76 //產生表格標題行

77 HSSFRow row = sheet.createRow(0);78 for (int i = 0; i < headers.length; i++) {79 HSSFCell cell =row.createCell(i);80 cell.setCellStyle(style);81 HSSFRichTextString text = newHSSFRichTextString(headers[i]);82 cell.setCellValue(text);83 }84 for (int i = 0; i < mapList.size(); i++) {85 row = sheet.createRow(i + 1);86 int j = 0;87 HSSFCell cell = row.createCell(j++);88 cell.setCellValue("循環獲得值1");89 cell.setCellStyle(style2);90 row.createCell(j++).setCellValue("循環獲得值2");91 row.createCell(j++).setCellValue("循環獲得值3");92 row.createCell(j++).setCellValue("循環獲得值4");93 }94 try{95 workbook.write(out);96 } catch(IOException e) {97 e.printStackTrace();98 }99 }100 }

View Code

packagetestweb;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;public classTestExcel {public static voidmain(String[] args) {try{

FileInputStream file= new FileInputStream("C:/Users/yanan/Desktop/yanan.xls");

List> yanantest =duquexcel(file);for (Mapitem : yanantest) {

System.out.println(item.get("id") + "," + item.get("name") + "," + item.get("gendar")+","+item.get("time"));

}

}catch(FileNotFoundException e) {

e.printStackTrace();

}

}public static List>duquexcel(InputStream fis) {

List> resultList = new ArrayList>();

HSSFWorkbook wb;try{

wb= new HSSFWorkbook(newPOIFSFileSystem(fis));

Sheet sheet= wb.getSheetAt(0);//日期格式化

SimpleDateFormat sdf = new SimpleDateFormat("yyyy/M/dd");//數字格式化

DecimalFormat df = new DecimalFormat("##");//循環xls中的每個表格

Row firstRow = sheet.getRow(0);for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {

Row row=sheet.getRow(i);

Map rowMap = new HashMap();for (int k = 0; k < row.getLastCellNum(); k++) {

Cell cell=row.getCell(k);if (null ==cell) {continue;

}switch(cell.getCellType()) {caseCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)){

rowMap.put(firstRow.getCell(k).getStringCellValue(),sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())) );break;

}double value_d =cell.getNumericCellValue();long value_l = (long) cell.getNumericCellValue();//cell.getCellStyle()獲取樣式

if (value_d ==value_l)

rowMap.put(firstRow.getCell(k).getStringCellValue(), String.valueOf(value_l));elserowMap.put(firstRow.getCell(k).getStringCellValue(), String.valueOf(value_d));break;caseCell.CELL_TYPE_STRING:

rowMap.put(firstRow.getCell(k).getStringCellValue(), cell.getStringCellValue());break;caseCell.CELL_TYPE_BLANK:break;default:

rowMap.put(firstRow.getCell(k).getStringCellValue(), cell.toString());break;

}

}//是否空行

if (rowMap.size() > 0) {

resultList.add(rowMap);

}

}catch(IOException e) {

e.printStackTrace();

}returnresultList;

}

View Code

20200606之前的寫法有些啰嗦，來個簡單點的@參考博客，@官方文檔，@參考地址3，@參考博客4

引入依賴

org.apache.poi

poi

4.1.2

View Code

代碼：

//得到Excel工作簿對象

HSSFWorkbook wb=newHSSFWorkbook ();//得到Excel工作表對象

HSSFSheet sheet =wb.createSheet("sheet名稱");//創建行

HSSFRow row0 =sheet.createRow(0);//創建單元格

row0.createCell(0).setCellValue("單元格值");

View Code

POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));//得到Excel工作簿對象

HSSFWorkbook wb = newHSSFWorkbook(fs);//得到Excel工作表對象

HSSFSheet sheet = wb.getSheetAt(0);//得到Excel工作表的行

HSSFRow row =sheet.getRow(i);//得到Excel工作表指定行的單元格

HSSFCell cell = row.getCell((short) j);

cellStyle= cell.getCellStyle();//得到單元格樣式

View Code

導出excel

//application/octet-stream

response.setCharacterEncoding("UTF-8");

response.setContentType("application/vnd.ms-excel");//生成xls的

response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("文件名稱", "UTF-8")+".xls"); response.addHeader("Pargam", "no-cache");

response.addHeader("Cache-Control", "no-cache");

OutputStream os=response.getOutputStream();

wb.write(os);

os.flush();

os.close();

wb.close();

View Code

java.net.URLEncoder.encode("文件名稱", "UTF-8")是為了防止文件名中文亂碼

僅靠上述代碼是文件有可能亂碼，即便進行了如下設置(測試發現，這樣寫也是沒起任何作用)

response.setContentType("application/ms-excel;charset=utf-8");

經驗證，在controller中添加produces屬性即可

@RequestMapping(value = { "/" }, produces = "application/octet-stream")

如果需要生成xlsx格式的代碼，需要調整一下。增加poi-ooxml依賴

org.apache.poi

poi

4.1.2

org.apache.poi

poi-ooxml

4.1.2

View Code

更改相關類

@RequestMapping(value = { "/exportAnswerExcel2" }, method = {RequestMethod.GET },produces = "application/octet-stream")

@ResponseBodypublic voidexportAnswerExcel2(HttpServletRequest req,HttpServletResponse res) {//得到Excel工作簿對象

Workbook wb= newXSSFWorkbook();//得到Excel工作表對象

Sheet sheet= wb.createSheet("工作簿名稱");//創建行

Row row0 = sheet.createRow(0);//創建單元格并賦值

row0.createCell(0).setCellValue("單元格值");

res.setCharacterEncoding("UTF-8");

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//生成xlsx的

res.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("excel文件名" "UTF-8")+".xlsx");

res.addHeader("Pargam", "no-cache");

res.addHeader("Cache-Control", "no-cache");

OutputStream os=res.getOutputStream();

wb.write(os);

os.flush();

os.close();

wb.close();

}

View Code

區別是

newHSSFWorkbook ();是用來生成xls的，newXSSFWorkbook();是用來生成xlsx的

20200702：生成word，參考博客：

java導出代碼：

public static void main(String[] args) throwsIOException {

XWPFDocument document= newXWPFDocument();//添加標題

XWPFParagraph titleParagraph =document.createParagraph();//設置段落居中

titleParagraph.setAlignment(ParagraphAlignment.CENTER);//標題

XWPFRun titleParagraphRun =titleParagraph.createRun();//然后把你查出的數據插入到document中去就可以了

titleParagraphRun.setText("AAA");//設置字體顏色

titleParagraphRun.setColor("000000");//設置字體大小

titleParagraphRun.setFontSize(15);

titleParagraphRun.setFontFamily("宋體");//段落

XWPFParagraph firstParagraph =document.createParagraph();

XWPFRun run=firstParagraph.createRun();

run.setText("BBB");

run.setColor("000000");

run.setFontSize(10);

run.setText("\tCCC?○⊙");

run.setColor("000000");

run.setFontSize(12);//換行

XWPFParagraph paragraph1 =document.createParagraph();

XWPFRun paragraphRun1=paragraph1.createRun();

paragraphRun1.setText("\r");

paragraph1.setPageBreak(true);//段落

XWPFParagraph firstParagraph2 =document.createParagraph();

XWPFRun run2=firstParagraph2.createRun();

run2.setText("BBB");

run2.setColor("000000");

run2.setFontSize(10);

run2.setText("\tCCC?");

run2.setColor("000000");

run2.setFontSize(12);

OutputStream os=new FileOutputStream("D:/2.docx");

document.write(os);

os.flush();

os.close();

document.close();

}

View Code

接口返回參考上面execl的改造一下就行了，不贅述了

總結

以上是生活随笔為你收集整理的java excel转word表格_java利用poi生成/读取excel表格、生成word的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： java file类详解_Java Fi
下一篇： ESN简介