[Java] 纯文本查看 复制代码
package com.lc.tablet.utils;
import android.content.Context;
import android.util.Log;
import android.util.Xml;
import android.widget.Toast;
import com.blankj.utilcode.util.LogUtils;
import com.lc.tablet.app.App;
import com.lc.tablet.app.Constants;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xmlpull.v1.XmlPullParser;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
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, List<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.size(); col++) {
sheet.addCell(new Label(col, 0, colName.get(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();
}
}
}
}
/**
* 初始化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 writeObjListToExcel(List<T> 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<String> list = (ArrayList<String>) 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) != null) {
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(App.getInstance(), fileName + "导出到sd卡文件夹(" + Constants.DATA_IMPORT + ")成功", Toast.LENGTH_LONG).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();
}
}
}
}
}
/**
* 读取Excel文件
*
* @param file
* @throws FileNotFoundException
*/
public static StringBuffer readExcelGps(File file) throws Exception {
StringBuffer stringBuffer = new StringBuffer();
try {
if (file.getAbsolutePath().endsWith(".xls")) {
InputStream stream = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = null;
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
sheet = workbook.getSheetAt(sheetIndex);// 获所有的sheet
String sheetName = workbook.getSheetName(sheetIndex); // sheetName
if (workbook.getSheetAt(sheetIndex) != null) {
sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
if (sheet != null) {
int firstRowNum = sheet.getFirstRowNum(); // 第一行
int lastRowNum = sheet.getLastRowNum(); // 最后一行
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
if (sheet.getRow(rowNum) != null) {// 如果行不为空，
HSSFRow row = sheet.getRow(rowNum);
short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
HSSFCell cell = row.getCell(cellNum);
if (cell != null) {
if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
// LogUtils.e(cell.toString());
//判断是否是经纬度
if (StringGPSUtils.isGpsLatitude(cell.toString()) || StringGPSUtils.isGpsLongitude(cell.toString())) {
stringBuffer.append(cell.toString()).append(" ");
}
}
}
}
stringBuffer.append("\r\n");
}
}
}
}
}
} else if (file.getAbsolutePath().endsWith(".xlsx")) {
List<String> ls = new ArrayList<String>();
ZipFile xlsxFile = new ZipFile(file);// 地址
ZipEntry sharedStringXML = xlsxFile
.getEntry("xl/sharedStrings.xml");// 共享字符串
InputStream inputStream = xlsxFile
.getInputStream(sharedStringXML);// 输入流 目标上面的共享字符串
XmlPullParser xmlParser = Xml.newPullParser();// new 解析器
xmlParser.setInput(inputStream, "utf-8");// 设置解析器类型
int evtType = xmlParser.getEventType();// 获取解析器的事件类型
while (evtType != XmlPullParser.END_DOCUMENT) {// 如果不等于 文档结束
switch (evtType) {
case XmlPullParser.START_TAG: // 标签开始
String tag = xmlParser.getName();
if (tag.equalsIgnoreCase("t")) {
String s = xmlParser.nextText();
LogUtils.e(s);
if (StringGPSUtils.isGpsLatitude(s) || StringGPSUtils.isGpsLongitude(s)) {
// stringBuffer.append(s).append(" ");
ls.add(s);
}
}
break;
case XmlPullParser.END_TAG: // 标签结束
// LogUtils.e("标签结束");
break;
default:
break;
}
evtType = xmlParser.next();
}
for (int i = 0; i < ls.size(); i++) {
if (i > 0 && i % 2 == 0) {
stringBuffer.append("\r\n");
}
stringBuffer.append(ls.get(i)).append(" ");
}
}
} catch (FileNotFoundException e) {
throw new Exception("文件 " + file.getName() + " 没有找到!");
} catch (IOException e) {
throw new Exception("文件 " + file.getName() + " 处理错误("
+ e.getMessage() + ")!");
}
LogUtils.e("stringBuffer"+stringBuffer.toString());
return stringBuffer;
}
/**
* 读取Excel文件
*
* @param file
* @throws FileNotFoundException
*/
public static void readExcel(File file) throws FileNotFoundException {
if (file == null) {
Log.e("NullFile", "读取Excel出错，文件为空文件");
return;
}
InputStream stream = new FileInputStream(file);
try {
XSSFWorkbook workbook = new XSSFWorkbook(stream);
XSSFSheet sheet = workbook.getSheetAt(0);
int rowsCount = sheet.getPhysicalNumberOfRows();
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (int r = 0; r < rowsCount; r++) {
Row row = sheet.getRow(r);
int cellsCount = row.getPhysicalNumberOfCells();
//每次读取一行的内容
for (int c = 0; c < cellsCount; c++) {
//将每一格子的内容转换为字符串形式
String value = getCellAsString(row, c, formulaEvaluator);
String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;
LogUtils.d(cellInfo);
}
}
} catch (Exception e) {
/* proper exception handling to be here */
LogUtils.e(e.toString());
}
}
/**
* 读取excel文件中每一行的内容
*
* @param row
* @param c
* @param formulaEvaluator
* @return
*/
private static String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) {
String value = "";
try {
Cell cell = row.getCell(c);
CellValue cellValue = formulaEvaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
value = "" + cellValue.getBooleanValue();
break;
case Cell.CELL_TYPE_NUMERIC:
double numericValue = cellValue.getNumberValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double date = cellValue.getNumberValue();
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yy");
value = formatter.format(HSSFDateUtil.getJavaDate(date));
} else {
value = "" + numericValue;
}
break;
case Cell.CELL_TYPE_STRING:
value = "" + cellValue.getStringValue();
break;
default:
break;
}
} catch (NullPointerException e) {
/* proper error handling should be here */
LogUtils.e(e.toString());
}
return value;
}
/**
* 根据类型后缀名简单判断是否Excel文件
*
* @param file 文件
* @Return 是否Excel文件
*/
public static boolean checkIfExcelFile(File file) {
if (file == null) {
return false;
}
String name = file.getName();
//”.“ 需要转义字符
String[] list = name.split("\\.");
//划分后的小于2个元素说明不可获取类型名
if (list.length < 2) {
return false;
}
String typeName = list[list.length - 1];
//满足xls或者xlsx才可以
return "xls".equals(typeName) || "xlsx".equals(typeName);
}
}
[复制链接]
发表于 2024-11-7 14:06
发表于 2024-11-7 15:36
|
发表于 2024-11-7 17:56
