我想将 Json 数组数据提取并显示到 Excel 工作表中 [英] I want to extract and display Json array data into Excel sheet
本文介绍了我想将 Json 数组数据提取并显示到 Excel 工作表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个名为任务列表的活动.这个任务列表包含 json 数组我现在有一个按钮，我想通过单击按钮将任务列表导出到 Excel 工作表中
对于创建 Excel 工作表，我使用了 poi-3.7.jar
我的 Excel 工作表代码:
public void onClick(View v) {//TODO 自动生成的方法存根开关 (v.getId()){案例R.id.param11:saveExcelFile(this,"myexcel.xls");休息;}}私有静态布尔 saveExcelFile(上下文上下文，字符串文件名){//检查是否可用而不是只读if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) {Log.e(TAG, "存储不可用或只读");返回假；}布尔成功 = 假；//新建工作簿工作簿 wb = 新的 HSSFWorkbook();单元格 c = 空；//标题行的单元格样式CellStyle cs = wb.createCellStyle();cs.setFillForegroundColor(HSSFColor.LIME.index);cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//新建工作表工作表 sheet1 = null;sheet1 = wb.createSheet("工作");//生成列标题org.apache.poi.ss.usermodel.Row row = sheet1.createRow(0);c = row.createCell(0);c.setCellValue("task_id");c.setCellStyle(cs);c = row.createCell(1);c = row.createCell(2);c.setCellValue("d_alias");c.setCellStyle(cs);c = row.createCell(3);c.setCellValue("worktype_name");c.setCellStyle(cs);c = row.createCell(4);c.setCellValue("状态");c.setCellStyle(cs);c = row.createCell(5);c.setCellValue("部门状态");c.setCellStyle(cs);c = row.createCell(6);c.setCellValue("优先级");c.setCellStyle(cs);c = row.createCell(7);c.setCellValue("staff_name");c.setCellStyle(cs);sheet1.setColumnWidth(0, (15 * 500));sheet1.setColumnWidth(1, (15 * 500));sheet1.setColumnWidth(2, (15 * 500));//创建一个路径，我们将把我们的对象列表放在外部存储上File file = new File(context.getExternalFilesDir(null), fileName);FileOutputStream os = null;尝试 {os = new FileOutputStream(file);wb.write(os);Log.w("FileUtils", "写入文件" + file);成功=真；} catch (IOException e) {Log.w("FileUtils", "写入错误" + file, e);} 捕获(异常 e){Log.w("FileUtils", "保存文件失败", e);} 最后 {尝试 {如果(空！= os)os.close();} 捕捉(异常前){}}返回成功；}public static boolean isExternalStorageReadOnly() {String extStorageState = Environment.getExternalStorageState();如果(Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)){返回真；}返回假；}公共静态布尔值 isExternalStorageAvailable() {String extStorageState = Environment.getExternalStorageState();如果(Environment.MEDIA_MOUNTED.equals(extStorageState)){返回真；}返回假；}///我已将json字符串转换为csv为了将 json 转换为 csv，我使用了 org.json.jar
if (array.length() > 0) {for (int i = 0; i < array.length(); i++) {JSONObject obj = (JSONObject) array.get(i);item.add(obj.getString("task_id").toString().trim());WorkItem item = new WorkItem(obj.getString("id"),obj.getString("task_id"),obj.getString("firm_name"),obj.getString("d_alias"),obj.getString("worktype_name"),obj.getString("状态"),obj.getString("部门状态"),obj.getString("优先级"),obj.getString("staff_name"),obj.getString("备注"));String csv = CDL.toString(array);System.out.println("csv " +csv);//csvrowItems.add(item);System.out.println("itemss" +item);}我的 Excel 表格创建成功，但它只包含标题
我的json字符串:
object[{"id":"10","task_id":"drm\/Technical \/75","firm_name":"dreamhome","d_alias":"Technical","worktype_name":"Technical","status":"completed","departmentstatus":"completed","priority":"high","staff_name":"anna","remark":"good","createddate":"2015-02-11 03:42:17","更新日期":"2015-02-11 03:42:17","isdeleted":"0"},{"id":"10","task_id":"sv\/Technical \/89","firm_name":"svye","d_alias":"技术","worktype_name":"aa","status":"已提交","departmentstatus":"未完成","priority":"5.0","staff_name":"sam","remark":"jdjd","createddate":"2015-02-12 23:59:05","updateddate":"2015-02-12 23:59:05","isdeleted":"0"}]//csv 字符串
02-18 14:41:43.531: I/System.out(854): 10,Technical,good,a,completed,high,0,Technical,drm/Technical/75,2015-02-11 03:42:17,2015-02-11 03:42:17,完成,devrai02-18 14:41:43.531: I/System.out(854): 10,aa,jdjd,Vishal,Submitted,5.0,0,Technical ,sv/Technical/89,2015-02-12 23:59:05,2015-02-12 23:59:05,不完整,devrai我不知道如何转换 csv 并将其保存到 Excel 表中.请帮助我如何实现这一目标.
解决方案
只需迭代 rowItems 并使用 createRow/createCell 不需要.csv
sheet1.setColumnWidth(2, (15 * 500));添加如下内容:
int row = 1;for(最终工作项:rowItems){最后一行行 = sheet1.createRow(row);整数单元格 = 0;Cell cell = row.createCell(cell++);cell.setCellValue(item.getTaskId());cell = row.createCell(cell++);cell.setCellValue(item.getDAlias());//...行 += 1;}I have one activity called task list.this task list contains json array I have one button now I want to export the task list into Excel sheet on button click
For Creating Excel sheet I have used poi-3.7.jar
my code for Excel sheet:
public void onClick(View v) {
// TODO Auto-generated method stub
switch (v.getId())
{
case R.id.param11:
saveExcelFile(this,"myexcel.xls");
break;
}
}
private static boolean saveExcelFile(Context context, String fileName) {
// check if available and not read only
if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) {
Log.e(TAG, "Storage not available or read only");
return false;
}
boolean success = false;
//New Workbook
Workbook wb = new HSSFWorkbook();
Cell c = null;
//Cell style for header row
CellStyle cs = wb.createCellStyle();
cs.setFillForegroundColor(HSSFColor.LIME.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//New Sheet
Sheet sheet1 = null;
sheet1 = wb.createSheet("wrok");
// Generate column headings
org.apache.poi.ss.usermodel.Row row = sheet1.createRow(0);
c = row.createCell(0);
c.setCellValue("task_id");
c.setCellStyle(cs);
c = row.createCell(1);
c = row.createCell(2);
c.setCellValue("d_alias");
c.setCellStyle(cs);
c = row.createCell(3);
c.setCellValue("worktype_name");
c.setCellStyle(cs);
c = row.createCell(4);
c.setCellValue("status");
c.setCellStyle(cs);
c = row.createCell(5);
c.setCellValue("departmentstatus");
c.setCellStyle(cs);
c = row.createCell(6);
c.setCellValue("priority");
c.setCellStyle(cs);
c = row.createCell(7);
c.setCellValue("staff_name");
c.setCellStyle(cs);
sheet1.setColumnWidth(0, (15 * 500));
sheet1.setColumnWidth(1, (15 * 500));
sheet1.setColumnWidth(2, (15 * 500));
// Create a path where we will place our List of objects on external storage
File file = new File(context.getExternalFilesDir(null), fileName);
FileOutputStream os = null;
try {
os = new FileOutputStream(file);
wb.write(os);
Log.w("FileUtils", "Writing file" + file);
success = true;
} catch (IOException e) {
Log.w("FileUtils", "Error writing " + file, e);
} catch (Exception e) {
Log.w("FileUtils", "Failed to save file", e);
} finally {
try {
if (null != os)
os.close();
} catch (Exception ex) {
}
}
return success;
}
public static boolean isExternalStorageReadOnly() {
String extStorageState = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) {
return true;
}
return false;
}
public static boolean isExternalStorageAvailable() {
String extStorageState = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED.equals(extStorageState)) {
return true;
}
return false;
}
///i have converted json string to csv
for converting json to csv i have used org.json.jar
if (array.length() > 0) {
for (int i = 0; i < array.length(); i++) {
JSONObject obj = (JSONObject) array.get(i);
item.add(obj.getString("task_id").toString().trim());
WorkItem item = new WorkItem(obj.getString("id"),
obj.getString("task_id"),
obj.getString("firm_name"),
obj.getString("d_alias"),
obj.getString("worktype_name"),
obj.getString("status"),
obj.getString("departmentstatus"),
obj.getString("priority"),
obj.getString("staff_name"),
obj.getString("remark"));
String csv = CDL.toString(array);
System.out.println("csv " +csv); //csv
rowItems.add(item);
System.out.println("itemss" +item);
}
My excelsheet is created successfully but it contains only headers
My json string:
object[{"id":"10","task_id":"drm\/Technical \/75","firm_name":"dreamhome","d_alias":"Technical ","worktype_name":"Technical","status":"completed","departmentstatus":"completed","priority":"high","staff_name":"anna","remark":"good","createddate":"2015-02-11 03:42:17","updateddate":"2015-02-11 03:42:17","isdeleted":"0"},{"id":"10","task_id":"sv\/Technical \/89","firm_name":"svye","d_alias":"Technical ","worktype_name":"aa","status":"Submitted","departmentstatus":"incomplete","priority":"5.0","staff_name":"sam","remark":"jdjd","createddate":"2015-02-12 23:59:05","updateddate":"2015-02-12 23:59:05","isdeleted":"0"}]
//csv string
02-18 14:41:43.531: I/System.out(854): 10,Technical,good,a,completed,high,0,Technical ,drm/Technical /75,2015-02-11 03:42:17,2015-02-11 03:42:17,completed,devrai
02-18 14:41:43.531: I/System.out(854): 10,aa,jdjd,Vishal,Submitted,5.0,0,Technical ,sv/Technical /89,2015-02-12 23:59:05,2015-02-12 23:59:05,incomplete,devrai
I dont have any idea how to convert csv and save it into excel sheet. Please help me how to achieve this.
解决方案
Just iterate over rowItems and use createRow / createCell no need for csv. After
sheet1.setColumnWidth(2, (15 * 500));
Add something like:
int row = 1;
for (final WorkItem item: rowItems) {
final Row row = sheet1.createRow(row);
int cell = 0;
Cell cell = row.createCell(cell++);
cell.setCellValue(item.getTaskId());
cell = row.createCell(cell++);
cell.setCellValue(item.getDAlias());
// ...
row += 1;
}
Some reading: Busy Developers' Guide to HSSF and XSSF Features
这篇关于我想将 Json 数组数据提取并显示到 Excel 工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
