JExcel API使用笔记
JExcel是一个开源的支持excel的java类库，广泛利用其api来生成excel报表
API基本使用
1.创建excel文件
workbook = Workbook.createWorkbook(file);//传入file文件
2.创建sheet页
WritableSheet sheet = workbook.createSheet("记录表", 0);//可以调整sheet页的名称与索引
3.创建标题栏
创建完sheet页后，我们先创建一行标题栏
sheet.addCell(new Label(c, r, fileName, arial14format));//r是行，c是列，标签内容，单元格格式
4.设置单元格行高、列宽
sheet.setRowView(0, 340); //设置行高
sheet.setColumnView(0, 45); //设置列宽
5.单元格格式设置
//字体设置
arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);//字体、大小、样式
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);//字体颜色
arial14format = new WritableCellFormat(arial14font);
arial14format.setAlignment(jxl.format.Alignment.CENTRE);//居中
arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//边界样式
arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);//背景颜色
6.添加普通标签
获取到WritableSheet的变量后，就能添加单元格了
sheet.addCell(new Label(5, 6, “hello”, arial14format));//在第5+1列第6+1行添加一个hello
行、列都是从0开始算的！
7.添加数字
sheet.addCell(new Number(i, j + 1, Integer.parseInt(list.get(i)), arial12format));
添加格式化后的数字
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = new WritableCellFormat(nf);
Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
8.添加日期
WritableCellFormat dateCellFormat = new WritableCellFormat(new DateFormat("yyyy/MM/dd HH:mm:ss"));
dateCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
dateCellFormat.setBackground(Colour.AQUA);
sheet.addCell(new DateTime(c, r, (Date) list.get(i), dateCellFormat));
9.添加链接
WritableHyperlink hyperlink = new WritableHyperlink(i, j, new URL("http://baidu.com"));
Label issueLink = new Label(i, j, "http://baidu.com", arial12format);
hyperlink.setDescription(issueLink.getContents());
sheet.addCell(issueLink);
sheet.addHyperlink(hyperlink);
10.添加Boolean对象
Boolean labelB = new jxl.write.Boolean(0, 2, false);
sheet.addCell(labelB);
11.添加图片
WritableImage wimage = new WritableImage(0, 1, 2, 2, image);//0,1分别代表x,y.2,2代表宽和高占的单元格数
//image文件或byte[]
sheet.addImage(wimage);
12.将列设置为自适应
CellView cellView = sheet.getColumnView(i);
cellView.setAutosize(true);
sheet.setColumnView(i, cellView);
13.写入工作表
writebook.write();
14.关闭工作表
writebook.close();
15.获取单元格的数据
public static void readExcel(String filePath) {
try {
InputStream is = new FileInputStream(filePath);
Workbook rwb = Workbook.getWorkbook(is);
//Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始
Sheet st = rwb.getSheet("original");
Cell c00 = st.getCell(0, 0);
//通用的获取cell值的方式,返回字符串
String strc00 = c00.getContents();
//获得cell具体类型值的方式
if (c00.getType() == CellType.LABEL) {
LabelCell labelc00 = (LabelCell) c00;
strc00 = labelc00.getString();
}
//输出
System.out.println(strc00);
//关闭
rwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
根据文件路径打开输入流，得到文件内容，获取到sheet，获取到（0，0）的内容，判断单元格中数据的格式，最后输出。
for (int j = 0;j<sheet.getColumns();j++){
StringBuffer sb = new StringBuffer();
for (int i=0;i<sheet.getRows();i++){
// 列,行
cell = sheet.getCell(j, i);
sb.append(cell.getContents());// 获取单元格内容
sb.append(",");// 将单元格的每行内容用逗号隔开
}
list.add(sb.toString());//将每行的字符串用一个String类型的集合保存。
}
循环获取内容，添加到集合中。
Android下简单使用
依赖
implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'
工具类
import android.content.Context;
import android.os.Environment;
import android.widget.Toast;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelUtil {
public static WritableFont arial14font = null;
public static WritableCellFormat arial14format = null;
public static WritableFont arial10font = null;
public static WritableCellFormat arial10format = null;
public static WritableFont arial12font = null;
public static WritableCellFormat arial12format = null;
public final static String UTF8_ENCODING = "UTF-8";
public final static String GBK_ENCODING = "GBK";
/**
* 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
*/
public static void format() {
try {
arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
arial14format = new WritableCellFormat(arial14font);
arial14format.setAlignment(jxl.format.Alignment.CENTRE);
arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
arial10format = new WritableCellFormat(arial10font);
arial10format.setAlignment(jxl.format.Alignment.CENTRE);
arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial10format.setBackground(Colour.GRAY_25);
arial12font = new WritableFont(WritableFont.ARIAL, 10);
arial12format = new WritableCellFormat(arial12font);
arial12format.setAlignment(jxl.format.Alignment.CENTRE);//对齐格式
arial10format.setAlignment(jxl.format.Alignment.CENTRE);//对齐格式
arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //设置边框
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
* 初始化Excel
*
* @param fileName
* @param colName
*/
public static void initExcel(String fileName, String[] colName) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("成绩表", 0);
//创建标题栏
sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
for (int col = 0; col < colName.length; col++) {
sheet.addCell(new Label(col, 0, colName[col], arial10format));
}
sheet.setRowView(0, 340); //设置行高
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@SuppressWarnings("unchecked")
public static <T> void writeListToExcel(List<T> objList, String fileName, Context c) {
if (objList != null && objList.size() > 0) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName), workbook);
WritableSheet sheet = writebook.getSheet(0);
for (int j = 0; j < objList.size(); j++) {
ArrayList<Object> list = (ArrayList<Object>) objList.get(j);
for (int i = 0; i < list.size(); i++) {
if (list.get(i) instanceof String) {
sheet.addCell(new Label(i, j + 1, (String) list.get(i), arial12format));
} else if (list.get(i) instanceof Integer) {
sheet.addCell(new Number(i, j + 1, (Integer) list.get(i), arial12format));
} else if (list.get(i) instanceof Double) {
sheet.addCell(new Number(i, j + 1, (Double) list.get(i), arial12format));
} else if (list.get(i) instanceof Date) {
WritableCellFormat dateCellFormat = new WritableCellFormat(new DateFormat("yyyy/MM/dd HH:mm:ss"));
dateCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
dateCellFormat.setBackground(Colour.AQUA);
sheet.addCell(new DateTime(i, j + 1, (Date) list.get(i), dateCellFormat));
} else {
WritableHyperlink hyperlink = new WritableHyperlink(i, j, new URL("http://jira.jd.com/browse/"));
Label issueLink = new Label(i, j, "http://jira.jd.com/browse/", arial12format);
hyperlink.setDescription(issueLink.getContents());
sheet.addCell(issueLink);
sheet.addHyperlink(hyperlink);
}
sheet.setColumnView(i, list.get(i).toString().length() + 5); //设置列宽
}
sheet.setRowView(j + 1, 350); //设置行高
}
expandColumns(sheet, objList.size());
// sheet.addCell(new Label(5, 6, "hello", arial14format));//在第7行第6列添加一个hello
writebook.write();
Toast.makeText(c, "Success", Toast.LENGTH_SHORT).show();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (writebook != null) {
try {
writebook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
/***
* 根据内容调整 列的宽度
* @param sheet
* @param columnSize
*/
private static void expandColumns(WritableSheet sheet, int columnSize) {
for (int i = 0; i < columnSize; i++) {
CellView cellView = sheet.getColumnView(i);
cellView.setAutosize(true);
sheet.setColumnView(i, cellView);
}
}
public static String getSDPath() {
File sdDir = null;
boolean sdCardExist = Environment.getExternalStorageState().equals(android.os.Environment.MEDIA_MOUNTED);
if (sdCardExist) {
sdDir = Environment.getExternalStorageDirectory();
}
String dir = sdDir.toString();
return dir;
}
public static void makeDir(File dir) {
if (!dir.getParentFile().exists()) {
makeDir(dir.getParentFile());
}
dir.mkdir();
}
}
调用
调用前需要先申请权限，次步骤略过
/**
* 导出excel
*/
public void exportExcel1() {
String[] title = {"观音心咒", "百字明", "顶礼", "35佛"};
File file = new File(ExcelUtil.getSDPath() + "/bluetooth");
ExcelUtil.makeDir(file);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = sdf.format(new Date());//Calendar.getInstance().toString();
String fileName = file.toString() + "/" + "20240614" + "-" + time + "修行量.xls";
ExcelUtil.initExcel(fileName, title);
ExcelUtil.writeListToExcel(getObjData(), fileName, this);
}
private ArrayList<ArrayList<Object>> getObjData() {
ArrayList<ArrayList<Object>> recordList = new ArrayList<>();
for (int i = 0; i < 30; i++) {
ArrayList<Object> beanList = new ArrayList<>();
beanList.add("里 " + i);
beanList.add(i + " 外");
beanList.add(30 * i);
beanList.add(12.23 * i);
beanList.add(new Date());
beanList.add(1f);
recordList.add(beanList);
}
return recordList;
}
注意：这个工具类是输出excel的，读取的excel不能是xlsx格式，需要自己转换后才能读。
899
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
