android 基于jexcel 实现导入导出
1.最终效果图
2.引入三方库： implementation group: ‘net.sourceforge.jexcelapi’, name: ‘jxl’, version: '2.6.12’
- 导出功能代码 实现
object UtilExcel {
fun exportExcel(file: File, data: List<SampleEntityNew>): Boolean {
var ok: Boolean
try {
val workbook = Workbook.createWorkbook(file)
val sheet = workbook.createSheet(
UtilTime.formatTime(
System.currentTimeMillis(),
UtilTime.YYMMDD
), 0
)
val normalFont = WritableFont(WritableFont.ARIAL, 12)
val boldFont = WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD)
val wcfCenter = WritableCellFormat(boldFont)
wcfCenter.setBorder(Border.ALL, BorderLineStyle.THIN) // 线条
wcfCenter.verticalAlignment = VerticalAlignment.CENTRE // 文字垂直对齐
wcfCenter.alignment = Alignment.CENTRE // 文字水平对齐
wcfCenter.wrap = false // 文字是否换行
// 用于正文居中
val wcfLeft = WritableCellFormat(normalFont)
wcfLeft.setBorder(Border.ALL, BorderLineStyle.THIN) // 线条
wcfLeft.verticalAlignment = VerticalAlignment.CENTRE // 文字垂直对齐
wcfLeft.alignment = Alignment.CENTRE // 文字水平对齐
wcfLeft.wrap = false // 文字是否换行
var row = 0
val titles = Controller.getContext().resources.getStringArray(R.array.excel_titles)
titles.forEachIndexed { index, value ->
sheet.addCell(Label(index, row, value, wcfCenter))
}
row += 1
data.forEachIndexed { _, item ->
row = addCell(sheet, wcfLeft, row, item)
}
workbook.write()
workbook.close()
ok = true
} catch (e: Exception) {
ok = false
}
return ok
}
private fun addCell(
sheet: WritableSheet,
format: WritableCellFormat,
startRow: Int,
item: SampleEntityNew
): Int {
var row = startRow
item.samplePros.forEach {
sheet.addCell(Label(0, row, item.sampleIndex.toString(), format))
sheet.addCell(Label(1, row, item.sampleNum, format))
sheet.addCell(Label(2, row, item.testStatus.toString(), format))
sheet.addCell(Label(3, row, item.testCardName, format))
sheet.addCell(Label(4, row, Controller.getProType(item.sampleType), format))
sheet.addCell(Label(5, row, it.proName, format))
// sheet.addCell(Label(6, row, it.result.toString(), format))
sheet.addCell(Label(6, row, it.resultDes, format))
sheet.addCell(
Label(
7,
row,
resultDescriptionConversionStr(it.concentrationResult),
format
)
)
val debug = it.proDebugRange
var strDebug = ""
if (debug.size == 1) strDebug = debug[0].toString()
else if (debug.size >= 2) {
strDebug = debug[0].toString().plus("-").plus(debug[1].toString())
}
sheet.addCell(Label(8, row, strDebug, format))
val refer = it.proReferenceRange
var strRefer = ""
if (refer.size == 1) strRefer = refer[0].toString()
else if (refer.size >= 2) {
strRefer = refer[0].toString().plus("-").plus(refer[1].toString())
}
sheet.addCell(Label(9, row, strRefer, format))
sheet.addCell(Label(10, row, it.proUnit, format))
sheet.addCell(Label(11, row, it.proDecimal.toString(), format))
sheet.addCell(Label(12, row, item.patientName, format))
sheet.addCell(Label(13, row, item.doctorName, format))
sheet.addCell(Label(14, row, item.doctorDepartment, format))
sheet.addCell(Label(15, row, item.department, format))
sheet.addCell(Label(16, row, item.hospitalName, format))
sheet.addCell(Label(17, row, item.creatTime, format))
sheet.addCell(Label(18, row, UtilTime.formatTimeFull(item.testTime), format))
row += 1
}
if (row - startRow >= 2) {
sheet.mergeCells(0, startRow, 0, row - 1)
sheet.mergeCells(1, startRow, 1, row - 1)
sheet.mergeCells(2, startRow, 2, row - 1)
sheet.mergeCells(3, startRow, 3, row - 1)
sheet.mergeCells(4, startRow, 4, row - 1)
sheet.mergeCells(12, startRow, 11, row - 1)
sheet.mergeCells(13, startRow, 12, row - 1)
sheet.mergeCells(14, startRow, 13, row - 1)
sheet.mergeCells(15, startRow, 14, row - 1)
sheet.mergeCells(16, startRow, 15, row - 1)
sheet.mergeCells(17, startRow, 16, row - 1)
sheet.mergeCells(18, startRow, 17, row - 1)
}
return row
}
}
- 导入功能代码实现 方式一
object UtilExcel {
fun importExcel(file: File, data: MutableList<SampleEntityNew>): Boolean {
val workbook = Workbook.getWorkbook(file)
val sheets = workbook.sheets
if (sheets.isEmpty()) {
workbook.close()
return false
} else {
val sheet = sheets[0]
val rows = sheet.rows
val columns = sheet.columns
if (rows <= 1 || columns <= 0) {
workbook.close()
return false
} else {
for (row in 1 until rows) {
for (column in 0 until columns) {
val str = sheet.getCell(column, row).contents
when (column) {
0 -> {
if (str.isNotEmpty()) {
val item = SampleEntityNew()
item.testStatus = TESTED
try {
item.sampleIndex = str.toInt()
} catch (e: java.lang.Exception) {
item.sampleIndex = -1
}
data.add(item)
}
}
1 -> {
if (str.isNotEmpty()) data.last().sampleNum = str
}
3 -> {
if (str.isNotEmpty()) data.last().testCardName = str
}
4 -> {
if (str.isNotEmpty()) data.last().sampleType =
Controller.getTypeIndex(str)
}
5 -> {
if (str.isNotEmpty()) {
val testPro = TestProEntity()
testPro.proName = str
data.last().samplePros.add(testPro)
}
}
6 -> {
if (str.isNotEmpty()) {
try {
data.last().samplePros.last().resultDes = str
} catch (e: java.lang.Exception) {
data.last().samplePros.last().result = -1.0
}
}
}
7 -> {
if (str.isNotEmpty()) {
try {
data.last().samplePros.last().concentrationResult =resultDescriptionConversionInt(str)
} catch (e: java.lang.Exception) {
data.last().samplePros.last().result = -1.0
}
}
}
8 -> {
if (str.isNotEmpty()) {
val testRange = str.split("-")
if (testRange.size >= 2) {
try {
data.last().samplePros.last().proDebugRange.add(
testRange[0].toDouble()
)
data.last().samplePros.last().proDebugRange.add(
testRange[1].toDouble()
)
} catch (e: Exception) {
data.last().samplePros.last().proDebugRange.clear()
}
}
}
}
9 -> {
if (str.isNotEmpty()) {
val testRange = str.split("-")
if (testRange.size >= 2) {
try {
data.last().samplePros.last().proReferenceRange.add(
testRange[0].toDouble()
)
data.last().samplePros.last().proReferenceRange.add(
testRange[1].toDouble()
)
} catch (e: Exception) {
data.last().samplePros.last().proReferenceRange.clear()
}
}
}
}
10 -> {
if (str.isNotEmpty()) {
data.last().samplePros.last().proUnit = str
}
}
11 -> {
if (str.isNotEmpty()) {
try {
data.last().samplePros.last().proDecimal = str.toInt()
} catch (e: Exception) {
data.last().samplePros.last().proDecimal = 4
}
}
}
12 -> {
if (str.isNotEmpty()) {
data.last().patientName = str
}
}
13 -> {
if (str.isNotEmpty()) {
data.last().doctorName = str
}
}
14 -> {
if (str.isNotEmpty()) {
data.last().doctorDepartment = str
}
}
15 -> {
if (str.isNotEmpty()) {
data.last().department = str
}
}
16 -> {
if (str.isNotEmpty()) {
data.last().hospitalName = str
}
}
17 -> {
if (str.isNotEmpty()) {
data.last().creatTime = str
}
}
18 -> {
if (str.isNotEmpty()) {
try {
data.last().testTime = UtilTime.formatFullTime2Long(str)
} catch (e: Exception) {
data.last().testTime = System.currentTimeMillis()
}
}
}
}
}
}
}
workbook.close()
return true
}
}
}
- 导入功能代码实现 方式二
/**
* 读取Excel 文件工具类
*
*/
public class ReadExcelUtil {
/**
*
* @param file 文件
* @return 返回 list<Map<String ,obj>>
*/
public List<Map<String, Object>> readExcelToList(File file) {
List<Map<String, Object>> list = new ArrayList<>();
try {
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
for (int i = 1; i < sheet.getRows(); i++) {
Map<String, Object> map = new HashMap<String, Object>();
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
map.put(sheet.getCell(j, 0).getContents(), cell.getContents());
}
list.add(map);
}
workbook.close();// 关闭
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 解析json 数据
* @param readExcelToList List<MutableMap<String, Any>>
* @return MutableList<SampleEntityNew>
*/
@SuppressLint("SimpleDateFormat")
fun parseExcelDataIntoList(readExcelToList: List<MutableMap<String, Any>>?): MutableList<SampleEntityNew>? {
var sampleEntityNewList: MutableList<SampleEntityNew>? = null
var sampleEntityNewList1: MutableList<SampleEntityNew>? = null
val obj = JSONObject()
var jsonArray: JSONArray
try {
jsonArray = JSONArray(Gson().toJson(readExcelToList))
obj.put("rows", jsonArray)
} catch (e: JSONException) {
e.printStackTrace()
}
val gson = Gson()
val hospital: Hospital = gson.fromJson(obj.toString(), Hospital::class.java)
if (hospital.rows.size > 0) {
/**
* interface non-thread-safe thread-safe
List ArrayList CopyOnWriteArrayList
Map HashMap ConcurrentHashMap
Set HashSet / TreeSet CopyOnWriteArraySet
Queue ArrayDeque / LinkedList ArrayBlockingQueue / LinkedBlockingQueue
Deque ArrayDeque / LinkedList LinkedBlockingDeque
*/
sampleEntityNewList = ArrayList()
(sampleEntityNewList as ArrayList<SampleEntityNew>).clear()
sampleEntityNewList = ArrayList()
sampleEntityNewList1?.clear()
var sampleEntityNew: SampleEntityNew? = null
for (hospitalItem in hospital.rows) {
if (hospitalItem.创建时间.isNotEmpty()) {
sampleEntityNew = SampleEntityNew()
}
if (sampleEntityNew == null) {
sampleEntityNew = SampleEntityNew();
}
val testProEntity = TestProEntity()
if (hospitalItem.项目类别.isNotEmpty()) {
testProEntity.proName = hospitalItem.项目类别
}
if (hospitalItem.单位.isNotEmpty()) {
testProEntity.proUnit = hospitalItem.单位
}
if (hospitalItem.结果.isNotEmpty()) {
testProEntity.resultDes = hospitalItem.结果
}
if (hospitalItem.结果描述.isNotEmpty()) {
val resultDescriptionConversionInt =
resultDescriptionConversionInt(hospitalItem.结果描述)
testProEntity.concentrationResult = resultDescriptionConversionInt
}
if (hospitalItem.保留小数位数.isNotEmpty()) {
testProEntity.proDecimal = hospitalItem.保留小数位数.toInt()
}
var proDebugRanges = mutableListOf<Double>()
var proReferenceRanges = mutableListOf<Double>()
if (hospitalItem.检测范围.isNotEmpty()) {
proDebugRanges.add(hospitalItem.检测范围.split("-")[0].toDouble())
proDebugRanges.add(hospitalItem.检测范围.split("-")[1].toDouble())
testProEntity.proDebugRange.addAll(proDebugRanges)
proReferenceRanges.add(hospitalItem.参考范围.split("-")[0].toDouble())
proReferenceRanges.add(hospitalItem.参考范围.split("-")[1].toDouble())
testProEntity.proReferenceRange.addAll(proReferenceRanges)
}
sampleEntityNew.samplePros.add(testProEntity)
if (hospitalItem.序列号.isNotEmpty()) {
sampleEntityNew.sampleIndex = hospitalItem.序列号.toInt()
}
if (hospitalItem.样本号.isNotEmpty()) {
sampleEntityNew.sampleNum = hospitalItem.样本号
}
if (hospitalItem.状态.isNotEmpty()) {
sampleEntityNew.testStatus = hospitalItem.状态.toInt()
} else {
sampleEntityNew.testStatus = 2
}
if (hospitalItem.项目名称.isNotEmpty()) {
sampleEntityNew.testCardName = hospitalItem.项目名称
}
if (hospitalItem.样本类型.isNotEmpty()) {
sampleEntityNew.sampleType = Controller.getTypeIndex(hospitalItem.样本类型)
}
if (hospitalItem.姓名.isNotEmpty()) {
sampleEntityNew.patientName = hospitalItem.姓名
}
if (hospitalItem.医生姓名.isNotEmpty()) {
sampleEntityNew.doctorName = hospitalItem.医生姓名
}
if (hospitalItem.医生部门.isNotEmpty()) {
sampleEntityNew.doctorDepartment = hospitalItem.医生部门
}
if (hospitalItem.住院号.isNotEmpty()) {
sampleEntityNew.department = hospitalItem.住院号
}
if (hospitalItem.医院名称.isNotEmpty()) {
PrintSetConfig.saveHospitalName(hospitalItem.医院名称)
sampleEntityNew.hospitalName = PrintSetConfig.getHospitalName()
}
if (hospitalItem.创建时间.isNotEmpty()) {
val sdf = SimpleDateFormat("yyyy-MM-dd")
val date = sdf.parse(hospitalItem.创建时间) as Date
sampleEntityNew.creatTime = UtilTime.formatTime(date.time, YYMMDD)
}
if (hospitalItem.测试时间.isNotEmpty()) {
sampleEntityNew.testTime = UtilTime.formatFullTime2Long(hospitalItem.测试时间)
}
sampleEntityNewList.add(sampleEntityNew)
}
sampleEntityNewList1 =
removeDuplicateOrder(sampleEntityNewList) as MutableList<SampleEntityNew>?
}
return sampleEntityNewList1
}
/**
* 数据去重
* @param sampleEntitys List<SampleEntityNew>
* @return new list
*/
private fun removeDuplicateOrder(sampleEntitys: List<SampleEntityNew>): List<SampleEntityNew>? {
val set: MutableSet<SampleEntityNew> =
TreeSet<SampleEntityNew> { a, b -> // 字符串则按照asicc码升序排列
a.testTime.compareTo(b.testTime)
}
set.addAll(sampleEntitys)
return ArrayList<SampleEntityNew>(set)
}
}
229
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
