业务需求,实现数据备份与数据迁移:1:把数据库里面数据批量生成excel表格:;2:读取Excel表格,批量导入设备。
public class CreateExlUtil {
private static final String TAG = “CreateExlUtil”;
private static List areaList = null;
private static String roadcode = null;
private static String sectionsname = null;
private static String sectionscode = null;
/**
* 增加Excel保存数据
*
* @param mContext :
* @param excelSave :excel保存路径(getExternalCacheDir() + File.separator + "TestExcel")
* @param excelName :excel表格名称("areainfo.xlsx")
*/
public static void initSaveExcel(Context mContext, String excelSave, String excelName) {
areaList = new ArrayList<>();
areaList.add(new Addressinfo("6124007", "富康大道交叉路口至两府路交叉路口路段", "103404"));
areaList.add(new Addressinfo("6234001", "滨河东路与富康大道交叉路口", "103402"));
areaList.add(new Addressinfo("6344001", "滨河东路与两府路交叉路口", "103404"));
//String数据内的字段也可以设置成中文、日文等其他语言
String[] title = {"roadcode", "sectionsname", "sectionscode", "imagename"};
File BuildDir = new File(mContext.getExternalCacheDir(), "TestExcel"); //打开UHFData目录,如不存在则生成
if (!BuildDir.exists()) BuildDir.mkdirs();
// 创建Excel工作薄
WritableWorkbook wwb;
// 在SD卡中,新建立一个jxl文件,并写入数据
try {
File file = new File(excelSave + File.separator + excelName);
wwb = Workbook.createWorkbook(new File(excelSave + File.separator + excelName));
Log.v(TAG, " create file success: " + excelSave + File.separator + excelName);
// 添加第一个工作表并设置第一个Sheet的名字
WritableSheet sheet = wwb.createSheet("workName", 0);
Label label;
for (int i = 0; i < title.length; i++) {
label = new Label(i, 0, title[i]);
// 将定义好的单元格添加到工作表中
try {
sheet.addCell(label);
} catch (WriteException e) {
e.printStackTrace();
}
}
//查找测试的数据
if (areaList.isEmpty()) {
Log.i(TAG, "data is null");
} else {
Log.i(TAG, "数据不为空 size:" + areaList.size());
//保存数字到单元格,需要使用jxl.write.Number 必须使用其完整路径,否则会出现错误
for (int i = 0; i < areaList.size(); i++) {
//添加道路代码
label = new Label(0, i + 1, areaList.get(i).getRoadcode());
sheet.addCell(label);
//添加路段名称
label = new Label(1, i + 1, areaList.get(i).getSectionsname());
sheet.addCell(label);
//路段代码
label = new Label(2, i + 1, areaList.get(i).getSectionscode());
sheet.addCell(label);
setTextColorAboutAlcohol(sheet, i);//设置字体颜色
//插入图片
setWidthAndHeight(sheet, i, "/sdcard/pictest.jpg");//这里传入需要插入图片的路径
Log.v(TAG, "insert image: " + i);
sheet.setColumnView(i + 1, 30);//设置宽度(第一个参数:多少列,第二个参数:宽度)
sheet.setRowView(i + 1, 1000);//设置行高
}
}
wwb.write(); //写入数据
wwb.close(); //关闭文件
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
* 设置Excel表格的宽高vs插入图片
* 此处**jxl只支持png格式的图片(如果保存的是jpg,那么最后一个参数传byte[])**
* x:距离左边第几行
* y:占据几个空格
* width:宽度(几个空格)
* height:高度(占据几个空格)
*
* @param i
* @param path :图片路径
*/
private static void setWidthAndHeight(WritableSheet sheet, int i, String path) {
File mFile = new File(path);
if (mFile.exists()) {
byte[] imageData = bitmapToByte(BitmapFactory.decodeFile(path));
sheet.addImage(new WritableImage(3, i + 1, 1, 1, imageData));
Log.v(TAG, "add image success: " + i);
} else {
Log.v(TAG, " picture is not exist!!!");
}
}
/**
* 读excel表数据
*/
public static void init_ReadExcel(String excelSave) {
Workbook book = null;
try {
book = Workbook.getWorkbook(new File(excelSave));
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
//获得第一个工作表对象
Sheet sheet = book.getSheet("workName");
int rows = sheet.getRows();
int cols = sheet.getColumns();
System.out.println("总列数:" + cols);
System.out.println("总行数:" + rows);
System.out.println("----------------------------");
//循环读取数据
for (int j = 1; j < rows; j++) {
for (int i = 0; i < cols; i++) {
System.out.println("第" + j + "行,第" + i + "列为:" + sheet.getCell(i, j).getContents());
//如果用户名和用户组好相同,则不插入,否则就插入
switch (i) {
case 0:
roadcode = sheet.getCell(i, j).getContents();
break;
case 1:
sectionsname = sheet.getCell(i, j).getContents();
break;
case 2:
sectionscode = sheet.getCell(i, j).getContents();
break;
case 3:
saveImageFromDisk(sheet, j);
break;
default:
Log.i(TAG, "未知参数!");
break;
}
}
Log.i(TAG, "Excel:第"
+ j
+ "条信息={"
+ "roadcode=" + roadcode
+ ",sectionsname=" + sectionsname
+ ",sectionscode=" + sectionscode
+ "}");
}
}
public static byte[] bitmapToByte(Bitmap bitmap) {
ByteArrayOutputStream bos = null;
try {
if (bitmap == null) {
return null;
}
bos = new ByteArrayOutputStream();
bitmap.compress(Bitmap.CompressFormat.JPEG, 100, bos);
return bos.toByteArray();
} catch (Exception e) {
return null;
} catch (OutOfMemoryError e) {
return null;
} finally {
try {
if (bos != null)
bos.close();
} catch (IOException e) {
return null;
}
}
}
/**
* 保存图片路径
*
* @param sheet
* @param j
*/
private static void saveImageFromDisk(Sheet sheet, int j) {
try {
int numberOfImages = sheet.getNumberOfImages();
int image_index = j - 1;
Log.v(TAG, " numberOfImages:" + numberOfImages + " image_index:" + image_index);
//图片的大小
if (image_index < numberOfImages && image_index >= 0 && sheet.getDrawing(image_index).getImageData().length > 0) {
//生成图片
byte[] getImageData = sheet.getDrawing(image_index).getImageData();
//数组转成bitmap,在此处保存图片到本地即可
Bitmap bitmap = bytesToBimap(getImageData);
Log.v(TAG, "get bitmap to save image: ");
}
} catch (Exception e) {
Log.v(TAG, " error: " + e.getMessage());
}
}
/**
* byte数组转Bitmap
*
* @param b
* @return
*/
public static Bitmap bytesToBimap(byte[] b) {
if (b.length != 0) {
return BitmapFactory.decodeByteArray(b, 0, b.length);
} else {
return null;
}
}
/**
* 设置字体各种参数:字体颜色
*
* @param sheet
*/
public static void setTextColorAboutAlcohol(WritableSheet sheet, int i) {
WritableCell writableCell = sheet.getWritableCell(2, i + 1);//获取需要修改的位置
WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
12, // 字号(默认:WritableFont.DEFAULT_POINT_SIZE)
WritableFont.NO_BOLD, // 粗体
false, // 斜体
UnderlineStyle.NO_UNDERLINE, // 下划线
Colour.RED, // 字体颜色
ScriptStyle.NORMAL_SCRIPT);
WritableCellFormat wcf = new WritableCellFormat(titleWf);
writableCell.setCellFormat(wcf);
Log.v(TAG, "change text color");
}
}
如下图片是生成excel表格
如下图片是读取excel表格日志:
温馨提示1:excel格式(xlsx、xls)这里用xls,否则office不支持
温馨提示2:这里是用jxl.jar实现的,如果数据量过大时,建议分段生成excel表格,否则容易出现内存溢出现象,记得加上读写权限