如何使⽤Java创建Excel（.xls和.xlsx）⽂件并写⼊数据1，需要依赖的jar包，
<!-- POI(operate excel) start -->
<!-- the version of the following POI packages must be consistent -->
<dependency>
使用dom4j解析xml文件<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- mvnrepository/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- mvnrepository/artifact/org.apache.poi/poi-ooxml-schemas -->
<!-- the dependent jar package to create .xlsx file  -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<!-- mvnrepository/artifact/lbeans/xmlbeans -->
<dependency>
<groupId>lbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- mvnrepository/artifact/commons-collections/commons-collections -->
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.2</version>
</dependency>
<!-- mvnrepository/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<!-- POI end -->
JAR packages that need to be dependent
2，创建Excel（.xls 和 .xlsx的⽅法）我详细地写了如何创建.xls ⽂件，创建.xlsx⽂件就是引⽤的类不⼀样，步骤基本上是⼀样的。
/**
*
*/
kia.jira.utils;
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.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author bpan
*
* created 2018年2⽉27⽇
*/
public class CreateExcelFile {
private static HSSFWorkbook hWorkbook = null;
private static XSSFWorkbook xWorkbook = null;
/**
* 判断⽂件是否存在.
* @param fileDir  ⽂件路径
* @return
*/
public static boolean fileExist(String fileDir){
boolean flag = false;
File file = new File(fileDir);
flag = ists();
return flag;
}
/**
* 判断⽂件的sheet是否存在.
* @param fileDir  ⽂件路径
* @param sheetName  表格索引名
* @return boolean
*/
public static boolean XlsSheetExist(String fileDir, String sheetName){
boolean flag = false;
File file = new File(fileDir);
if (ists()) {
//⽂件存在，创建workbook
try {
hWorkbook = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = Sheet(sheetName);
if (sheet!=null) {
//⽂件存在，sheet存在
flag = true;
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else {
//⽂件不存在
flag = false;
}
return flag;
}
/**
* 创建新excel(xls).
* @param fileDir excel的路径
* @param sheetNames 要创建的表格索引列表
* @param titleRow  excel的第⼀⾏即表格头
*/
public static void createExcelXls(String fileDir, List<String> sheetNames, String titleRow[]){ //创建workbook
hWorkbook = new HSSFWorkbook();
//新建⽂件
FileOutputStream fileOutputStream = null;
HSSFRow row = null;
try {
CellStyle cellStyle = ateCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
//添加Worksheet（不添加sheet时⽣成的xls⽂件打开时会报错)
for(int i = 0; i<sheetNames.size(); i++){
<(i));
<(i)).createRow(0);
//添加表头, 创建第⼀⾏
row = (i)).createRow(0);
row.setHeight((short)(20*20));
for (short j = 0; j < titleRow.length; j++) {
HSSFCell cell = ateCell(j, CellType.BLANK);
cell.setCellValue(titleRow[j]);
cell.setCellStyle(cellStyle);
}
fileOutputStream = new FileOutputStream(fileDir);
hWorkbook.write(fileOutputStream);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if (fileOutputStream != null) {
try {
fileOutputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* 删除⽂件.
* @param fileDir  ⽂件路径
* @return如果⽂件不存在返回false, 如果⽂件存在删除成功之后返回true
*/
public static boolean deleteExcel(String fileDir) {
boolean flag = false;
File file = new File(fileDir);
// 判断⽬录或⽂件是否存在
if (!ists()) {  // 不存在返回 false
return flag;
} else {
// 判断是否为⽂件
if (file.isFile()) {  // 为⽂件时调⽤删除⽂件⽅法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 往excel(xls)中写⼊(已存在的数据⽆法写⼊).
* @param fileDir    ⽂件路径
* @param sheetName  表格索引
* @param object
* @throws Exception
*/
public static void writeToExcelXls(String fileDir, String sheetName, List<Map<String,String>> mapList) throws Exception{ //创建workbook
File file = new File(fileDir);
try {
hWorkbook = new HSSFWorkbook(new FileInputStream(file));
}catch(FileNotFoundException e){
e.printStackTrace();
}catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//⽂件流
FileOutputStream fileOutputStream = null;
HSSFSheet sheet = Sheet(sheetName);
// 获取表格的总⾏数
// int rowCount = LastRowNum() + 1; // 需要加⼀
//获取表头的列数
int columnCount = Row(0).getLastCellNum();
try {
// 获得表头⾏对象
HSSFRow titleRow = Row(0);
/
/创建单元格显⽰样式
CellStyle cellStyle = ateCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
if(titleRow!=null){
for(int rowId = 0; rowId < mapList.size(); rowId++){
Map<String,String> map = (rowId);
HSSFRow ateRow(rowId+1);
newRow.setHeight((short)(20*20));//设置⾏⾼基数为20
for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {  //遍历表头                        //trim()的⽅法是删除字符串中⾸尾的空格
String mapKey = Cell(columnIndex).toString().trim();
HSSFCell cell = ateCell(columnIndex);
cell.setCellStyle(cellStyle);
cell.(mapKey)==null ? null : (mapKey).toString());
}
}
}
fileOutputStream = new FileOutputStream(fileDir);
hWorkbook.write(fileOutputStream);
} catch (Exception e) {
throw e;
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 创建Excel(xlsx)
* @param fileDir  ⽂件名称及地址
* @param sheetName sheet的名称
* @param titleRow  表头
*/
public static void createExcelXlsx(String fileDir, String sheetName, String titleRow[]){
}
public static void main(String[] args) {
String fileDir = "d:\\workbook.xls";
List<String> sheetName = new ArrayList<>();
sheetName.add("A");
sheetName.add("B");
sheetName.add("C");
System.out.println(sheetName);
String[] title = {"id","name","password"};
List<Map<String,String>> userList1 = new ArrayList<Map<String,String>>();
Map<String,String> map=new HashMap<String,String>();
map.put("id", "111");
map.put("name", "张三");
map.put("password", "111！@#");
Map<String,String> map2=new HashMap<String,String>();
map2.put("id", "222");
map2.put("name", "李四");
map2.put("password", "222！@#");
Map<String,String> map3=new HashMap<String,String>();
map3.put("id", "33");
map3.put("name", "王五");
map3.put("password", "333！@#");
userList1.add(map);
userList1.add(map2);
userList1.add(map3);
Map<String, List<Map<String, String>>> users = new HashMap<>();
users.put("A", userList1);
List<Map<String,String>> userList2 = new ArrayList<Map<String,String>>();
Map<String,String> map4=new HashMap<String,String>();
map4.put("id", "111");
map4.put("name", "张三");
map4.put("password", "111！@#");
Map<String,String> map5=new HashMap<String,String>();
map5.put("id", "222");
map5.put("name", "李四");
map5.put("password", "222！@#");
Map<String,String> map6=new HashMap<String,String>();
map6.put("id", "33");
map6.put("name", "王五");
map6.put("password", "333！@#");
userList2.add(map4);
userList2.add(map5);
userList2.add(map6);
users.put("B", userList2);
List<Map<String,String>> userList3 = new ArrayList<Map<String,String>>();
users.put("C", userList3);
System.out.println(sheetName.size());
//删除List 集合中特定的元素
for(Iterator<String> sheeNameIterator = sheetName.iterator();sheeNameIterator.hasNext();){            String sheet = ();
if ( (sheet).size() == 0) {
}
}
System.out.println(sheetName.size());
createExcelXls(fileDir, sheetName, title);
for (int j = 0; j < sheetName.size(); j++) {
try {
writeToExcelXls(fileDir, (j), ((j)));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Create Excel File
3，⾄于如何设置单元格的样式，⼤家可以根据⾃⼰的兴趣去研究⼀下。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。