android 输出xlsx文件格式,Android导出Excel表格文件

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

*/

public static void writeObjListToExcel(List 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 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

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">

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" />

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" />

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" />

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 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中添加读写权限

tools:ignore="ProtectedPermissions" />

到此处项目就可以运行了，点击导出后，就可以再文件中找到导出的文件了

添加数据格式的问题

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