binbinyang博客----关于Android数据库导出到Excel
栏目：综合技术时间：2016-07-26 13:44:48
可能很多做JAVAWEB 或是C#的工程师。在项目特别是OA项目中，会用到POI。。。也就是excel的导入导出。。。恰好今天在研究安卓APP中，把数据填入到EditText中，然后自动保存数据到excel里面---------------用到了Sqlite及导出到Excel文件
1.首先里面要导入1个包。叫做JXL
<span style="font-size:18px;">Jxl使用总结
Jxl是1个开源的Java Excel API项目，通过Jxl，Java可以很方便的操作微软的Excel文档。除Jxl以外，还有Apache的1个POI项目，也能够操作Excel，二者相比之下：Jxl使用方便，但功能相对POI比较弱。POI使用复杂，上手慢，除这个没啥说的了。</span>
1.1
API总结
1、创建或读取1个工作薄 Workbook
创建1个工作薄，就是全部Excel文档，
WritableWorkbook wwb = Workbook.createWorkbook(os);
其中os为1个文件输出流。固然还有很多其他的入参，比如File等。
Workbook不但能用来创建工作薄，也能够读取现有的工作薄，比如：
Workbook.getWorkbook(java.io.File file);
Workbook是1个很重要工具类，里面方法基本上都是static的，使用方便。
2、创建工作表 Sheet
创建工作表的方式是通过上面创建的WritableWorkbook对象来操作。
创建1个工作表：
createSheet(java.lang.String name, int index)，
两个参数分别是工作表名字和插入位置，这个位置从0开始，比如： WritableSheet sheet = wwb.createSheet("演员表", 0);
3、创建标签 Label
实际上标签这里的意思就是工作表的单元格，这个单元格多种，分别对应不同的类，比如jxl.write.Boolean、jxl.write.Boolean等。
Label label = new Label(col, row, title);
3个参数分别表示col+1列，row+1行，标题内容是title。
将标签加入到工作表中
sheet.addCell(label);
4、填充数据
数据填充这块略微复杂点，触及到数据单元格的格式问题。
a）、填充数字
jxl.write.Number numb = new jxl.write.Number(1, 1, 250);
sheet.addCell(numb);
b）、填充格式化的数字 jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);
jxl.write.Number n = new jxl.write.Number(2, 1, 2.451, wcf);
sheet.addCell(n);
c）、填充日期 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String newdate = sdf.format(new Date());
label = new Label(2, 2, newdate);
sheet.addCell(label);
d）、填充文本 label = new Label(3, 3, "周星驰");
sheet.addCell(label);
e）、填充boolean值 jxl.write.Boolean bool = new jxl.write.Boolean(4, 1, true);
sheet.addCell(bool);
5、合并单元格
通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
表示将从第x+1列，y+1行到m+1列，n+1行合并 (4个点定义了两个坐标，左上角和右下角)
结果是合并了m-x+1行，n-y+1列，二者乘积就是合并的单元格数量。
sheet.mergeCells(0, 6, 3, 8);
label = new Label(0, 6, "合并了12个单元格");
sheet.addCell(label);
6、添加单元格的式样
主要是改变单元格背景、字体、色彩等等。
WritableCellFormat wc = new WritableCellFormat();
// 设置居中
wc.setAlignment(Alignment.CENTRE);
// 设置边框线
wc.setBorder(Border.ALL, BorderLineStyle.THIN);
// 设置单元格的背景色彩
wc.setBackground(jxl.format.Colour.RED);
label = new Label(1, 5, "字体", wc);
sheet.addCell(label);
7、设置单元格字体
// 设置字体
jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("楷书"), 20);
WritableCellFormat font = new WritableCellFormat(wfont);
label = new Label(2, 6, "楷书", font);
sheet.addCell(label);
8、将工作写成文件
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
9、行列的批量操作
//获得所有的工作表
jxl.write.WritableSheet[] sheetList = wwb.getSheets();
//获得第1列所有的单元格
jxl.Cell[] cellc = sheet.getColumn(0);
//获得第1行所有的单元格
jxl.Cell[] cellr = sheet.getRow(0);
//获得第1行第1列的单元格
Cell c = sheet.getCell(0, 0);
10、获得单元格的值
//获得单元格的值，不管甚么单元格，返回都是字符串
String value = c.getContents();
自己写的1个小小的记帐工具，用到了Sqlite及导出到Excel文件
先说说DB
先说说DB
public class DBHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "ldm_family"; // DB name
private Context mcontext;
private DBHelper mDbHelper;
private SQLiteDatabase db;
public DBHelper(Context context) {
super(context, DB_NAME, null, 11);
this.mcontext = context;
}
public DBHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
/**
* 用户第1次使用软件时调用的操作，用于获得数据库创建语句（SW）,然后创建数据库
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table if not exists family_bill(id integer primary key,time text,food text,use text,traffic text,travel text,clothes text,doctor text,laiwang text,baby text,live text,other text,remark text)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/* 打开数据库,如果已打开就使用，否则创建 */
public DBHelper open() {
if (null == mDbHelper) {
mDbHelper = new DBHelper(mcontext);
}
db = mDbHelper.getWritableDatabase();
return this;
}
/* 关闭数据库 */
public void close() {
db.close();
mDbHelper.close();
}
/**添加数据 */
public long insert(String tableName, ContentValues values) {
return db.insert(tableName, null, values);
}
/**查询数据*/
public Cursor findList(String tableName, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
return db.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
}
public Cursor exeSql(String sql) {
return db.rawQuery(sql, null);
}
}
然后看MainActivity 主界面
<span style="color:#555555;">public class MainActivity extends Activity implements OnClickListener {
private EditText mFoodEdt;
private EditText mArticlesEdt;
private EditText mTrafficEdt;
private EditText mTravelEdt;
private EditText mClothesEdt;
private EditText mDoctorEdt;
private EditText mRenQingEdt;
private EditText mBabyEdt;
private EditText mLiveEdt;
private EditText mOtherEdt;
private EditText mRemarkEdt;
private Button mSaveBtn;
private File file;
private String[] title = { "日期", "食品支出", "大保健费", "交通话费", "旅游出行", "穿着支出", "医疗保健", "人情客往", "宝宝专项", "房租水电", "其它支出", "备注说明" };
private String[] saveData;
private DBHelper mDbHelper;
private ArrayList<ArrayList<String>>bill2List;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewsById();
mDbHelper = new DBHelper(this);
mDbHelper.open();
bill2List=new ArrayList<ArrayList<String>>();
}
/**
* 声明VIEW
*/
private void findViewsById() {
mFoodEdt = (EditText) findViewById(R.id.family_bill_food_edt);
mArticlesEdt = (EditText) findViewById(R.id.family_bill_articles_edt);
mTrafficEdt = (EditText) findViewById(R.id.family_bill_traffic_edt);
mTravelEdt = (EditText) findViewById(R.id.family_bill_travel_edt);
mClothesEdt = (EditText) findViewById(R.id.family_bill_clothes_edt);
mDoctorEdt = (EditText) findViewById(R.id.family_bill_doctor_edt);
mRenQingEdt = (EditText) findViewById(R.id.family_bill_laiwang_edt);
mBabyEdt = (EditText) findViewById(R.id.family_bill_baby_edt);
mLiveEdt = (EditText) findViewById(R.id.family_bill_live_edt);
mOtherEdt = (EditText) findViewById(R.id.family_bill_other_edt);
mRemarkEdt = (EditText) findViewById(R.id.family_bill_remark_edt);
mSaveBtn = (Button) findViewById(R.id.family_bill_save);
mSaveBtn.setOnClickListener(this);
}
/**
* 点击事件
*/
@Override
public void onClick(View v)
</span>
关于EXCEL 部份
CreateExcel
public class CreateExcel {
// 准备设置excel工作表的标题
private WritableSheet sheet;
/**创建Excel工作薄*/
private WritableWorkbook wwb;
private String[] title = { "日期", "食品支出", "大保健费", "交通话费", "旅游出行", "穿着支出", "医疗保健", "人情客往", "宝宝专项", "房租水电", "其它支出", "备注说明" };
public CreateExcel() {
excelCreate();
}
public void excelCreate() {
try {
/**输出的excel文件的路径*/
String filePath = Environment.getExternalStorageDirectory() + "/family_bill";
File file = new File(filePath, "bill.xls");
if (!file.exists()) {
file.createNewFile();
}
wwb = Workbook.createWorkbook(file);
/**添加第1个工作表并设置第1个Sheet的名字*/
sheet = wwb.createSheet("家庭帐务表", 0);
}
catch (Exception e) {
e.printStackTrace();
}
}
public void saveDataToExcel(int index, String[] content) throws Exception {
Label label;
for (int i = 0; i < title.length; i++) {
/**Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
* 在Label对象的子对象中指明单元格的位置和内容
* */
label = new Label(i, 0, title[i]);
/**将定义好的单元格添加到工作表中*/
sheet.addCell(label);
}
/*
* 把数据填充到单元格中
* 需要使用jxl.write.Number
* 路径必须使用其完全路径，否则会出现毛病
*/
for (int i = 0; i < title.length; i++) {
Label labeli = new Label(i, index, content[i]);
sheet.addCell(labeli);
}
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
}
}
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⑻";
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(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 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));
}
workbook.write();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (workbook != null) {
try {
workbook.close();
}
catch (Exception e) {
// TODO Auto-generated catch block
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);
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));
}
}
writebook.write();
Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).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();
}
}
}
}
}
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;
}
}
<span style="font-size:14px;">public class SaveToExcel {
static HashMap map = new HashMap();
/*
* 这个更全
*/
public static void main(String[] args) {
try {
// copyDateFormat(new File("c:\\a.xls"), 0, "c:\\copy of a.xls");
writeExcelUseFormat("c:\\format.xls", "test");
// buildNewFormTemplete(new File("c:/templete.xls"),new File(
// "c:/buildNewFormTemplete.xls"));
// modifyDirectly1(new File("c:/templete.xls"));
// modifyDirectly2(new File("c:/templete.xls"));
// copyDateAndFormat(new File("c:/a.xls"), 0, "c:/a2.xls");
}
catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
public static void modifyDirectly2(File inputFile) throws Exception {
Workbook w1 = Workbook.getWorkbook(inputFile);
WritableWorkbook w2 = Workbook.createWorkbook(inputFile, w1);
WritableSheet sheet = w2.getSheet(0);
WritableCell cell = null;
CellFormat cf = null;
// 加粗
cell = sheet.getWritableCell(0, 0);
WritableFont bold = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
cf = new WritableCellFormat(bold);
cell.setCellFormat(cf);
// 设置下划线
cell = sheet.getWritableCell(0, 1);
WritableFont underline = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE);
cf = new WritableCellFormat(underline);
cell.setCellFormat(cf);
// 直截添加可以覆盖掉
setCellValueDirectly(sheet, sheet.getCell(0, 2), new Double(4), CellType.NUMBER);
w2.write();
w2.close();
}
public static void modifyDirectly1(File file) {
try {
// Excel取得文件
Workbook wb = Workbook.getWorkbook(file);
// 打开1个文件的副本，并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(file, wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加1个工作表
WritableSheet sheet = book.createSheet(" 第2页 ", 1);
sheet.addCell(new Label(0, 0, " 第2页的测试数据 "));
book.write();
book.close();
}
catch (Exception e) {
System.out.println(e);
}
}
public static void buildNewFormTemplete(File inputFile, File outputFile) {
try {
// Excel取得文件
Workbook wb = Workbook.getWorkbook(inputFile);
// 打开1个文件的副本，并且指定数据写回到原文件
WritableWorkbook book = Workbook.createWorkbook(outputFile, wb);
WritableSheet sheet0 = book.getSheet(0);
sheet0.addCell(new Label(0, 1, "陈小稳"));
// 添加1个工作表
WritableSheet sheet = book.createSheet(" 第2页 ", 1);
sheet.addCell(new Label(0, 0, " 第2页的测试数据 "));
book.write();
book.close();
}
catch (Exception e) {
System.out.println(e);
}
}
public static void copyDateAndFormat(File inputFile, int inputFileSheetIndex, String outputFilePath) throws Exception {
Workbook book = null;
Cell cell = null;
// 1.避免乱码的设置
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh", "CN");
setting.setLocale(locale);
setting.setEncoding("ISO⑻859⑴");
book = Workbook.getWorkbook(inputFile, setting);
Sheet readonlySheet = book.getSheet(inputFileSheetIndex);
OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL
WritableWorkbook wwb = Workbook.createWorkbook(os);// 创建可写工作薄
WritableSheet writableSheet = wwb.createSheet(readonlySheet.getName(), 0);// 创建可写工作表
// 2.誊写不同数据格式的数据
for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
cell = readonlySheet.getCell(colIndex, rowIndex);
// A2B2为合并的单元格，A2有内容，B2为空
// if(colIndex == 0 && rowIndex == 1){
// System.out.println(colIndex + "," + rowIndex + " type:" +
// cell.getType() +" :" + cell.getContents());
// }
// 【有各种设置格式】
if (cell.getType() == CellType.DATE || cell.getType() == CellType.DATE_FORMULA) {
writableSheet.addCell(new jxl.write.DateTime(colIndex, rowIndex, ((DateCell) cell).getDate(), new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else if (cell.getType() == CellType.NUMBER || cell.getType() == CellType.NUMBER_FORMULA) {
writableSheet.addCell(new jxl.write.Number(colIndex, rowIndex, ((jxl.NumberCell) cell).getValue(), new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else if (cell.getType() == CellType.EMPTY) {
// 空的和合并单元格中第1列外的
// System.out.println("EMPTY:"+cell.getContents());
// System.err.println("空单元格 at " + colIndex + "," + rowIndex
// +" content:" + cell.getContents());
}
else if (cell.getType() == CellType.LABEL || cell.getType() == CellType.STRING_FORMULA) {
writableSheet.addCell(new Label(colIndex, rowIndex, cell.getContents(), new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else {
System.err.println("其它单元格类型：" + cell.getType() + " at " + colIndex + "," + rowIndex + " content:" + cell.getContents());
}
// if(cell.getType() == CellType.STRING_FORMULA){
// System.err.println(colIndex + "," + rowIndex +":" +
// cell.getContents() +" type:" + cell.getType());
// }
}
}
// 3.处理合并单元格的事情(复制合并单元格格式)
Range[] range = readonlySheet.getMergedCells();
for (int i = 0; i < range.length; i++) {
// System.out.println("第"+i+"处合并的单元格:"
// +",getTopLeft="+range[i].getTopLeft().getColumn()
// +","+range[i].getTopLeft().getRow()
// +",getBottomRight="+range[i].getBottomRight().getColumn()
// +","+range[i].getBottomRight().getRow()
// );
// topleftXIndex, topleftYIndex, bottomRightXIndex,
// bottomRightYIndex
writableSheet.mergeCells(range[i].getTopLeft().getColumn(), range[i].getTopLeft().getRow(), range[i].getBottomRight().getColumn(), range[i].getBottomRight().getRow());
}
// 4.设置行列高宽
for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
writableSheet.setColumnView(colIndex, readonlySheet.getColumnView(colIndex));
}
for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
writableSheet.setRowView(rowIndex, readonlySheet.getRowView(rowIndex));
}
wwb.write();
wwb.close();
os.close();
}
public static void writeExcelUseFormat(String outputFilePath, String outputFileSheetName) throws Exception {
OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL
WritableWorkbook wwb = Workbook.createWorkbook(os);// 创建可写工作薄
WritableSheet sheet = wwb.createSheet(outputFileSheetName, 0);// 创建可写工作表
sheet.addCell(new Label(0, 0, "号码"));
sheet.addCell(new Label(1, 0, "有效期"));
// 1.写入时间的数据格式
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");
jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1, new Date(), wcfDF); // 自定义格式
sheet.addCell(labelDTF);
// 2.字体样式
// WritableFont()方法里参数说明：
// 这个方法算是1个容器，可以放进去好多属性
// 第1个: TIMES是字体大小，他写的是18
// 第2个: BOLD是判断是不是为斜体,选择true时为斜体
// 第3个: ARIAL
// 第4个: UnderlineStyle.NO_UNDERLINE 下划线
// 第5个: jxl.format.Colour.RED 字体色彩是红色的
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setWrap(true);// 自动换行
wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 把水平对齐方式指定为居中
wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 把垂直对齐方式指定为居中
jxl.write.Label labelC = new jxl.write.Label(0, 1, "This is a Label cell", wcfF);
sheet.addCell(labelC);
// 3.添加带有formatting的Number对象
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
jxl.write.Number labelNF = new jxl.write.Number(0, 2, 3.1415926, wcfN);
sheet.addCell(labelNF);
// 4.添加Boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(0, 3, false);
sheet.addCell(labelB);
// 5.设置1个注解
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("添加Boolean对象");
labelB.setCellFeatures(cellFeatures);
// 6.单元格内换行
WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
wrappedText.setWrap(true);// 可换行的label样式
Label label = new Label(4, 0, "测试,\012测试。。。", wrappedText); // "\012"强迫换行
sheet.addCell(label);
// 7.数字的公式计算
jxl.write.Number n = new jxl.write.Number(0, 9, 4.5);// A10
sheet.addCell(n);
n = new jxl.write.Number(1, 9, 8);// B10
sheet.addCell(n);
NumberFormat dp3 = new NumberFormat("#.###"); // 设置单元格里面的数字格式
WritableCellFormat dp3cell = new WritableCellFormat(dp3);
dp3cell.setWrap(true);
Formula f = new Formula(2, 9, "(a10+b10)/2", dp3cell); // 设置C10公式
sheet.addCell(f);
f = new Formula(3, 9, "SUM(A10:B10)", dp3cell);// 设置D10公式
sheet.addCell(f);
// 8.设置sheet的样式
sheet.getSettings().setProtected(true); // 设置xls的保护，单元格为只读的
sheet.getSettings().setPassword("123"); // 设置xls的密码
sheet.getSettings().setDefaultColumnWidth(10); // 设置列的默许宽度,2cm左右
sheet.setRowView(3, 200);// 设置第4行高度
sheet.setRowView(2, false);// 这样可以自动把行高扩大
sheet.setColumnView(0, 300);// 设置第1列宽度，6cm左右
sheet.mergeCells(0, 5, 1, 7);// 合并单元格：合并A6B8也就是1列6行 与 2列7行之间的矩形
// 9.设置边框
drawRect(sheet, 5, 6, 7, 6, BorderLineStyle.THICK, Colour.BLACK, null);
sheet.mergeCells(1, 2, 3, 3);
wwb.write();
wwb.close();
os.close();
}
public static void drawRect(WritableSheet sheet, int x, int y, int width, int height, BorderLineStyle style, Colour BorderColor, Colour bgColor) throws WriteException {
for (int w = 0; w < width; w++) {
for (int h = 0; h < height; h++) {
WritableCellFormat alignStyle = new WritableCellFormat(); // 单元格样式
alignStyle.setAlignment(Alignment.CENTRE); // 设置对齐方式
alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
if (h == 0) // 画上
alignStyle.setBorder(Border.TOP, style, BorderColor);// 设置边框的色彩和样式
if (w == 0) // 画左
alignStyle.setBorder(Border.LEFT, style, BorderColor);// 设置边框的色彩和样式
if (w == width - 1) // 画右
alignStyle.setBorder(Border.RIGHT, style, BorderColor);// 设置边框的色彩和样式
if (h == height - 1) // 画下
alignStyle.setBorder(Border.BOTTOM, style, BorderColor);// 设置边框的色彩和样式
// drawLine(sheet, x, y, Border.BOTTOM);
if (bgColor != null) alignStyle.setBackground(bgColor); // 背静色
Label mergelabel = new Label(x, y, "", alignStyle);
// topleftXIndex, topleftYIndex, bottomRightXIndex,
// bottomRightYIndex
// sheet.mergeCells(2, 5, 10, 10);
sheet.addCell(mergelabel);
y++;
}
y -= height;
x++;
}
}
public static ArrayList<String> sampleReadExcel(File inputFile, int inputFileSheetIndex) throws Exception {
ArrayList<String> list = new ArrayList<String>();
Workbook book = null;
Cell cell = null;
// 避免乱码的设置
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh", "CN");
setting.setLocale(locale);
setting.setEncoding("ISO⑻859⑴");
book = Workbook.getWorkbook(inputFile, setting);
Sheet sheet = book.getSheet(inputFileSheetIndex);
for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {// Excel第1行动表头,因此J初值设为1
for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {// 只需从Excel中取出2列
cell = sheet.getCell(colIndex, rowIndex);
list.add(cell.getContents());
}
}
// 【问题：如果在实际部署的时候没有写下面这句是不是会致使不断消耗掉服务器的内存？jxl里面有个ReadWrite.java没有关闭读的，只关闭了写的】
book.close();
return list;
}
public static void setCellValueDirectly(WritableSheet sheet, Cell cell, Object newValue, CellType type) throws Exception {
if (type == CellType.DATE || type == CellType.DATE_FORMULA) {
sheet.addCell(new jxl.write.DateTime(cell.getColumn(), cell.getRow(), (Date) newValue, new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else if (type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {
sheet.addCell(new jxl.write.Number(cell.getColumn(), cell.getRow(), ((Double) newValue).doubleValue(), new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else if (type == CellType.LABEL || type == CellType.STRING_FORMULA) {
sheet.addCell(new Label(cell.getColumn(), cell.getRow(), (String) newValue, new jxl.write.WritableCellFormat(cell.getCellFormat())));
}
else {
throw new Exception("不支持的其它单元格类型：" + type);
// System.err.println("不支持的其它单元格类型：" + cell.getType() + " at " +
// cell.getColumn() + "," + cell.getRow() +" current content:" +
// cell.getContents());
}
}
}</span><span style="font-size:24px;">
</span>
------分隔线----------------------------
上一篇 gcc编译解析
------分隔线----------------------------