JExcel API使用笔记

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列添加一个hellowritebook.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格式，需要自己转换后才能读。

参考链接

JExcel API使用笔记

java 读取数据库数据生成excel文件

Android Jxl读写Excel

Jxl 包下载

Java Excel API

2.6.10 API