Android 导入与导出Excel文件案例

2024-04-21 20:01| 来源: 网络整理| 查看: 265

将数据导入与导出Excel文件案例

1、引入Excel 依赖包

在app的build.gradle里面添加依赖包:

implementation 'net.sourceforge.jexcelapi:jxl:2.6.12'

2、编写ExcelUtils 工具类

package com.rfid.uhfsdktest.util; import android.Manifest; import android.app.Activity; import android.content.pm.PackageManager; import android.support.v4.app.ActivityCompat; import android.util.Log; import com.rfid.uhfsdktest.bean.InventoryBuffer; import com.rfid.uhfsdktest.bean.OperateTagBuffer; import com.rfid.uhfsdktest.R; import com.rfid.uhfsdktest.UHFApplication; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import java.util.Locale; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.read.biff.BiffException; 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; import jxl.write.biff.RowsExceededException; 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"; private final static String[] mContent = new String[]{"ID", "EPC", "PC", UHFApplication.getContext().getResources().getString(R.string.real_list_times), "RSSI",UHFApplication.getContext().getString(R.string.real_list_freq), UHFApplication.getContext().getString(R.string.tag_inventory_time)}; private final static String[] mOperateData = new String[] { "ID","PC","CRC","EPC",UHFApplication.getContext().getString(R.string.data), UHFApplication.getContext().getString(R.string.data_length), UHFApplication.getContext().getString(R.string.list_antenna_port), UHFApplication.getContext().getString(R.string.access_list_times) }; private final static String FILE_SUFFIX = ".xls"; // Storage Permissions private static final int REQUEST_EXTERNAL_STORAGE = 1; private static String[] PERMISSIONS_STORAGE = { Manifest.permission.READ_EXTERNAL_STORAGE, Manifest.permission.WRITE_EXTERNAL_STORAGE }; 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(jxl.format.Colour.LIGHT_BLUE); arial12font = new WritableFont(WritableFont.ARIAL, 12); arial12format = new WritableCellFormat(arial12font); arial12format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); } catch (WriteException e) { e.printStackTrace(); } } public static void write(String inputFile) { try { inputFile = inputFile.trim() + FILE_SUFFIX; File file = new File(inputFile); WorkbookSettings wbSettings = new WorkbookSettings(); wbSettings.setLocale(new Locale("en", "EN")); WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings); workbook.createSheet("Report", 0); WritableSheet excelSheet = workbook.getSheet(0); workbook.write(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } } 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("tags", 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)); } workbook.write(); } catch (Exception e) { e.printStackTrace(); } finally { if (workbook != null) { try { workbook.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } public static void writeOperateTagToExcel (String fileName, List maps) { fileName = fileName.trim() + FILE_SUFFIX; initExcel(fileName,mOperateData); writeOperateTagListToExcel(maps,fileName); } public static void writeTagToExcel(String fileName, List maps) { fileName = fileName.trim() + FILE_SUFFIX; initExcel(fileName,mContent); writeObjListToExcel(maps,fileName); } @SuppressWarnings("unchecked") public static void writeObjListToExcel(List objList,String fileName) { 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 list = new ArrayList(); InventoryBuffer.InventoryTagMap map = ((InventoryBuffer.InventoryTagMap)(objList.get(j))); list.add(j + 1 + ""); list.add(map.strEPC); list.add(map.strPC); list.add(map.nReadCount + ""); list.add((Integer.parseInt(map.strRSSI) - 129) + "dBm"); list.add(map.strFreq); list.add(new SimpleDateFormat("yyyy/MM/dd").format(map.mDate) + " " + new SimpleDateFormat("kk:mm:ss").format(map.mDate)); for (int i = 0; i < list.size(); i++) { sheet.addCell(new Label(i, j + 1, list.get(i), arial12format)); } } writebook.write(); } 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(); } } } } } @SuppressWarnings("unchecked") public static void writeOperateTagListToExcel(List objList, String fileName) { 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 list = new ArrayList(); OperateTagBuffer.OperateTagMap map = ((OperateTagBuffer.OperateTagMap)(objList.get(j))); list.add(j + 1 + ""); list.add(map.strPC); list.add(map.strCRC); list.add(map.strEPC); list.add(map.strData); list.add(map.nDataLen + ""); list.add(map.btAntId + ""); list.add(map.nReadCount + ""); for (int i = 0; i < list.size(); i++) { sheet.addCell(new Label(i, j + 1, list.get(i),arial12format)); } } writebook.write(); } 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(); } } } } } public static Object getValueByRef(Class cls, String fieldName) { Object value = null; fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName .substring(0, 1).toUpperCase()); String getMethodName = "get" + fieldName; try { Method method = cls.getMethod(getMethodName); value = method.invoke(cls); } catch (Exception e) { e.printStackTrace(); } return value; } /** * Checks if the app has permission to write to device storage * * If the app does not has permission then the user will be prompted to grant permissions * * @param activity */ public static void verifyStoragePermissions(Activity activity) { // Check if we have write permission int permission = ActivityCompat.checkSelfPermission(activity, Manifest.permission.WRITE_EXTERNAL_STORAGE); if (permission != PackageManager.PERMISSION_GRANTED) { // We don't have permission so prompt the user ActivityCompat.requestPermissions( activity, PERMISSIONS_STORAGE, REQUEST_EXTERNAL_STORAGE ); } } public static void readExcel(String filepath) { try { /** * 后续考虑问题,比如Excel里面的图片以及其他数据类型的读取 **/ InputStream is = new FileInputStream(filepath); Workbook book = Workbook.getWorkbook(new File(filepath)); book.getNumberOfSheets(); // 获得第一个工作表对象 Sheet sheet = book.getSheet(0); int Rows = sheet.getRows(); int Cols = sheet.getColumns(); System.out.println("当前工作表的名字:" + sheet.getName()); System.out.println("总行数:" + Rows); System.out.println("总列数:" + Cols); for (int i = 0; i < Cols; ++i) { for (int j = 0; j < Rows; ++j) { // getCell(Col,Row)获得单元格的值 System.out.print((sheet.getCell(i, j)).getContents() + "\t"); } System.out.print("\n"); } // 得到第一列第一行的单元格 Cell cell1 = sheet.getCell(0, 0); String result = cell1.getContents(); System.out.println(result); book.close(); } catch (Exception e) { System.out.println(e); } } }

3、调用方式

3.1、导出excel调用方式：

private List mTags = new ArrayList(); final String file = SDCardRootDir.getAbsolutePath() + "/" + mDirectory.getText().toString() + "/" + mFileName.getText().toString() ; ExcelUtils.writeTagToExcel(file,data);

3.2、导入excel调用方式：

String filePath = Environment.getExternalStorageDirectory() + File.separator +"test.xls" ; ExcelUtils.readExcel(filePath);

【本文地址】

公司简介

联系我们