jxl 新版电子表格_Android使用jxl快速生成Excel表

前言

在Android开发时，有些时候需要把app中List集合数据，导出到Excel表中，方便进一步操作。Android可以使用jxl或poi来导出Excel，关于jxl和poi的比较网上有很多说法，不过统一的说法就是jxl的操作比poi简单，但是其功能略低于poi，但是个人认为对于简单的表格导出，使用jxl就足够了。本文以jxl为例，快速导出Excel表格。

先看看效果图：

第一步、下载并导入jxl.jar包

jxl.jar包的下载地址：http://jexcelapi.sourceforge.net/

大家也可以到我的项目工程中直接拷贝，jar包的导入这里就不详细说明了。下面上代码，生成上图的成绩表。

第二步、生成表格

模拟数据、调用工具类生成表格

package teprinciple.yang.list2excel;

import android.os.Environment;

import android.support.v7.app.AppCompatActivity;

import android.os.Bundle;

import android.view.View;

import com.shidian.excel.ExcelUtils;

import java.io.File;

import java.util.ArrayList;

import java.util.List;

public class MainActivity extends AppCompatActivity {

private ArrayList> recordList;

private List students;

private static String[] title = { "编号","姓名","性别","年龄","班级","数学","英语","语文" };

private File file;

private String fileName;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

//模拟数据集合

students = new ArrayList<>();

for (int i = 1; i <= 10; i++) {

students.add(new Student("小红"+i,"女","12","1"+i,"一班","85","77","98"));

students.add(new Student("小明"+i,"男","14","2"+i,"二班","65","57","100"));

}

}

/**

* 导出excel

* @param view

*/

public void exportExcel(View view) {

file = new File(getSDPath() + "/Record");

makeDir(file);

ExcelUtils.initExcel(file.toString() + "/成绩表.xls", title);

fileName = getSDPath() + "/Record/成绩表.xls";

ExcelUtils.writeObjListToExcel(getRecordData(), fileName, this);

}

/**

* 将数据集合 转化成ArrayList>

* @return

*/

private ArrayList> getRecordData() {

recordList = new ArrayList<>();

for (int i = 0; i

Student student = students.get(i);

ArrayList beanList = new ArrayList();

beanList.add(student.id);

beanList.add(student.name);

beanList.add(student.sex);

beanList.add(student.age);

beanList.add(student.classNo);

beanList.add(student.math);

beanList.add(student.english);

beanList.add(student.chinese);

recordList.add(beanList);

}

return recordList;

}

private 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 void makeDir(File dir) {

if (!dir.getParentFile().exists()) {

makeDir(dir.getParentFile());

}

dir.mkdir();

}

}

生成表格的核心工具类:

package com.shidian.excel;

import android.content.Context;

import android.util.Log;

import android.widget.Toast;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.Colour;

import jxl.write.Label;

import jxl.write.WritableCell;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

public class ExcelUtils {

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);

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 void writeObjListToExcel(List 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);

// sheet.mergeCells(0,1,0,objList.size()); //合并单元格

// sheet.mergeCells()

for (int j = 0; j < objList.size(); j++) {

ArrayList list = (ArrayList) objList.get(j);

for (int i = 0; i < list.size(); i++) {

sheet.addCell(new Label(i, j + 1, list.get(i),arial12format));

if (list.get(i).length() <= 5){

sheet.setColumnView(i,list.get(i).length()+8); //设置列宽

}else {

sheet.setColumnView(i,list.get(i).length()+5); //设置列宽

}

}

sheet.setRowView(j+1,350); //设置行高

}

writebook.write();

Toast.makeText(c, "导出到手机存储中文件夹Record成功", 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();

}

}

}

}

}

}

这样就在手机的文件夹中生成成绩表.xls表。

注意：需要添加WRITE_EXTERNAL_STORAGE权限