参考自：
https://blog.csdn.net/yangbin0513/article/details/51782525
https://blog.csdn.net/qq_36982160/article/details/82421940
1、首先再Android添加jar包：jxl-2.6.12.jar
2、创建javaBean类，用于存储需要写入表格中的数据
public class DemoBean {
private String name;
private int age;
private boolean boy;
public DemoBean(String name, int age, boolean boy) {
this.name = name;
this.age = age;
this.boy = boy;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public boolean isBoy() {
return boy;
}
public void setBoy(boolean boy) {
this.boy = boy;
}
}
3、创建工具类：ExcelUtil, 注释非常详细就不解释了
import android.content.Context;
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.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelUtil {
private static WritableFont arial14font = null;//可写字体
private static WritableCellFormat arial14format = null;//单元格格式
private static WritableFont arial10font = null;
private static WritableCellFormat arial10format = null;
private static WritableFont arial12font = null;
private static WritableCellFormat arial12format = null;
private final static String UTF8_ENCODING = "UTF-8";
//单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
private static void format() {
try {
//字体 ARIAL， 字号 14 bold 粗体
arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
arial14font.setColour(Colour.LIGHT_BLUE);//字体的颜色
arial14font.setUnderlineStyle(UnderlineStyle.SINGLE);//设置下划线
//初始化单元格格式
arial14format = new WritableCellFormat(arial14font);
arial14format.setAlignment(Alignment.CENTRE);//对齐方式
arial14format.setBorder(Border.ALL, BorderLineStyle.THIN);//边框的格式
arial14format.setBackground(Colour.VERY_LIGHT_YELLOW);//底色
arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
arial10format = new WritableCellFormat(arial10font);
arial10format.setAlignment(Alignment.CENTRE);
arial10format.setBorder(Border.ALL, BorderLineStyle.THIN);
arial10format.setBackground(Colour.GRAY_25);
arial12font = new WritableFont(WritableFont.ARIAL, 10);
arial12format = new WritableCellFormat(arial12font);
arial12format.setAlignment(Alignment.CENTRE);
arial12format.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
* 初始化Excel
* 写入字段名称，表名
*
* @param filePath 导出excel的存放地址
* @param sheetName Excel表格的表名
* @param colName excel中包含的列名
*/
public static void initExcel(String filePath, String sheetName, String[] colName) {
format();
//创建一个工作薄，就是整个Excel文档
WritableWorkbook workbook = null;
try {
File file = new File(filePath);
if (!file.exists()) {
file.createNewFile();
}
//使用Workbook创建一个工作薄，就是整个Excel文档
workbook = Workbook.createWorkbook(file);
//设置表格的名称(两个参数分别是工作表名字和插入位置，这个位置从0开始)
WritableSheet sheet = workbook.createSheet(sheetName, 0);
//创建label标签：实际就是单元格的标签（三个参数分别是：col + 1列，row + 1行， 内容， 单元格格式）
// Label label = new Label(0, 0, filePath, arial14format);//设置第一行的单元格标签为：标题
//将标签加入到工作表中
// sheet.addCell(label);
//通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
// 表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)
sheet.mergeCells(0, 0, colName.length - 1, 0);
sheet.addCell(new Label(0, 0, "我是标题", arial14format));
sheet.setRowView(0, 520);
//再同一个单元格中写入数据，上一个数据会被下一个数据覆盖
for (int col = 0; col < colName.length; col++) {
sheet.addCell(new Label(col, 1, colName[col], arial10format));
}
//设置行高 参数的意义为（第几行， 行高）
sheet.setRowView(1, 340);
workbook.write();// 写入数据
} catch (IOException | WriteException e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();// 关闭文件
} catch (IOException | WriteException e) {
e.printStackTrace();
}
}
}
}
/**
* 见指定类型的List写入到Excel文件中
*
* @param objList 代写入的List
* @param fileName
* @param context
* @param <T>
*/
public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context context) {
if (objList != null && objList.size() > 0) {
//创建一个工作薄，就是整个Excel文档
WritableWorkbook writeBook = null;
InputStream in = null;
try {
WorkbookSettings settings = new WorkbookSettings();
settings.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(fileName));
//Workbook不但能用来创建工作薄，也可以读取现有的工作薄
Workbook workbook = Workbook.getWorkbook(in);
//创建一个工作薄，就是整个Excel文档
writeBook = Workbook.createWorkbook(new File(fileName), workbook);
//读取表格
WritableSheet sheet = writeBook.getSheet(0);
for (int j = 0; j < objList.size(); j++) {
DemoBean demoBean = (DemoBean) objList.get(j);
List<String> list = new ArrayList<>();
list.add(demoBean.getName());
list.add(String.valueOf(demoBean.getAge()));
list.add(String.valueOf(demoBean.isBoy()));
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(i, j + 2, list.get(i), arial12format));//向一行中添加数据
if (list.get(i).length() <= 4) {
//设置列宽
sheet.setColumnView(i, list.get(i).length() + 8);
} else {
sheet.setColumnView(i, list.get(i).length() + 5);
}
}
//设置行高
sheet.setRowView(j + 1, 350);
}
writeBook.write();
workbook.close();
Toast.makeText(context, "导出Excel成功", Toast.LENGTH_SHORT).show();
} catch (IOException | BiffException | WriteException e) {
e.printStackTrace();
} finally {
if (writeBook != null) {
try {
writeBook.close();
} catch (IOException | WriteException e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
}
4、创建XML文件 activity_save_excel.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent">
<TextView
android:id="@+id/textView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_above="@+id/export_button"
android:layout_centerHorizontal="true"
android:gravity="center_horizontal"
tools:layout_editor_absoluteX="176dp"
tools:layout_editor_absoluteY="161dp" />
<Button
android:id="@+id/export_button"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_centerInParent="true"
android:layout_marginTop="20dp"
android:text="导出"
tools:layout_editor_absoluteX="161dp"
tools:layout_editor_absoluteY="341dp" />
<Button
android:id="@+id/open_button"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@id/export_button"
android:layout_centerInParent="true"
android:layout_marginTop="20dp"
android:text="打开"
tools:layout_editor_absoluteX="161dp"
tools:layout_editor_absoluteY="341dp" />
</RelativeLayout>
5、创建SaveExcelActivity.java
public class SaveExcelActivity extends AppCompatActivity implements View.OnClickListener {
private TextView textView;
private AlertDialog alertDialog;
String[] permissions = {Manifest.permission.READ_EXTERNAL_STORAGE, Manifest.permission.WRITE_EXTERNAL_STORAGE};
private int REQUEST_PERMISSION_CODE = 1000;
@SuppressLint("SdCardPath")
private String filePath = "/sdcard/AndroidExcelDemo";
//请求权限
private void requestPermission() {
if (Build.VERSION.SDK_INT > 23) {
if (ContextCompat.checkSelfPermission(SaveExcelActivity.this, permissions[0]) == PackageManager.PERMISSION_GRANTED) {
LogUtil.e("requestPermission:" + "用户之前已经授予了权限！");
} else {
requestPermissions(permissions, REQUEST_PERMISSION_CODE);
}
}
}
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_save_excel);
requestPermission();
StrictMode.VmPolicy.Builder builder = new StrictMode.VmPolicy.Builder();
StrictMode.setVmPolicy(builder.build());
builder.detectFileUriExposure();
Button exportButton = findViewById(R.id.export_button);
exportButton.setOnClickListener(this);
Button openButton = findViewById(R.id.open_button);
openButton.setOnClickListener(this);
textView = findViewById(R.id.textView);
}
@Override
public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
super.onRequestPermissionsResult(requestCode, permissions, grantResults);
if (requestCode == REQUEST_PERMISSION_CODE) {
if (grantResults.length > 0 && grantResults[0] == PackageManager.PERMISSION_GRANTED) {
LogUtil.e("申请成功");
} else {
AlertDialog.Builder builder = new AlertDialog.Builder(SaveExcelActivity.this);
builder.setTitle("permission");
builder.setMessage("点击允许才可以使用");
builder.setPositiveButton("去允许", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
if (alertDialog != null && alertDialog.isShowing()) {
alertDialog.dismiss();
}
ActivityCompat.requestPermissions(SaveExcelActivity.this, new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE}, 1);
}
});
alertDialog = builder.create();
alertDialog.setCanceledOnTouchOutside(false);
alertDialog.show();
}
}
}
private void showDialogTipUserRequestPermission() {
ActivityCompat.requestPermissions(this, permissions, 321);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.export_button:
exportExcel(this);
break;
case R.id.open_button:
openDir();
default:
break;
}
}
private void openDir() {
File file = new File(filePath);
Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
intent.setType("*/*");
intent.addCategory(Intent.CATEGORY_OPENABLE);
intent.setDataAndType(Uri.fromFile(file), "file/*");
try {
startActivity(intent);
} catch (Exception e) {
Toast.makeText(this, "没用正确打开文件管理器", Toast.LENGTH_SHORT).show();
}
}
//导出
private void exportExcel(Context context) {
File file = new File(filePath);
if (!file.exists()) {
file.mkdirs();
}
String excelFileName = "/demo.xls";
String[] title = {"姓名", "年龄", "男孩"};
String sheetName = "表格名称";
List<DemoBean> demoBeanList = new ArrayList<>();
DemoBean demoBean1 = new DemoBean("张三", 10, false);
DemoBean demoBean2 = new DemoBean("李四", 11, true);
DemoBean demoBean3 = new DemoBean("王二", 12, true);
DemoBean demoBean4 = new DemoBean("麻子", 13, true);
demoBeanList.add(demoBean1);
demoBeanList.add(demoBean2);
demoBeanList.add(demoBean3);
demoBeanList.add(demoBean4);
filePath = filePath + excelFileName;//文件的路径
LogUtil.e(filePath);
ExcelUtil.initExcel(filePath, sheetName, title);
ExcelUtil.writeObjListToExcel(demoBeanList, filePath, context);
textView.setText("excel已导出: " + filePath);
}
}
6、再AndroidManifest.xml中添加读写权限
<uses-permission android:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"
tools:ignore="ProtectedPermissions" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
<!-- 往SDCard写入数据权限 -->
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
到此处项目就可以运行了，点击导出后，就可以再文件中找到导出的文件了
添加数据格式的问题
a、填充数字
jxl.write.Number numb = new jxl.write.Number(1, 1, 250);
sheet.addCell(numb);
b、填充格式化的数字
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf); jxl.write.Number n = new jxl.write.Number(2, 1, 2.451, wcf);
sheet.addCell(n);
c、填充日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String newdate = sdf.format(new Date());
label = new Label(2, 2, newdate);
sheet.addCell(label);
d、填充文本
label = new Label(3, 3, "周星驰");
sheet.addCell(label);
e、填充boolean值
jxl.write.Boolean bool = new jxl.write.Boolean(4, 1, true);
sheet.addCell(bool);
获取单元格的数据
//获取所有的工作表
jxl.write.WritableSheet[] sheetList = wwb.getSheets();
//获取第1列所有的单元格
jxl.Cell[] cellc = sheet.getColumn(0);
//获取第1行所有的单元格
jxl.Cell[] cellr = sheet.getRow(0);
//获取第1行第1列的单元格
Cell c = sheet.getCell(0, 0);
//获取单元格的值，不管什么单元格，返回都是字符串
String value = c.getContents();
