南宁做网站找哪家公司,商场设计平面图,型云网站建设,河北项目网在建项目JExcel API使用笔记
JExcel是一个开源的支持excel的java类库#xff0c;广泛利用其api来生成excel报表
API基本使用
1.创建excel文件
workbook Workbook.createWorkbook(file);//传入file文件2.创建sheet页
WritableSheet sheet workbook.createSheet(记录表广泛利用其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));//在第51列第61行添加一个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获取到00的内容判断单元格中数据的格式最后输出。 for (int j 0;jsheet.getColumns();j){StringBuffer sb new StringBuffer();for (int i0;isheet.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(ListT 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) {ArrayListObject list (ArrayListObject) 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 ArrayListArrayListObject getObjData() {ArrayListArrayListObject recordList new ArrayList();for (int i 0; i 30; i) {ArrayListObject 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
