Generate an excel file in android

Generate an excel file in android

It is said that programmers are not happy with product managers. In fact, sometimes some wonderful background developers will be uncomfortable. A recent project requires that an excel file be generated and then sent to the customer. As a result, the background staff directly threw this function to the client on the grounds that the background is not easy to implement. When you hear this, you can only implement it by yourself (in minutes you may want a screw king to buckle it ). In this blog, the following excel tables are generated in android and stored locally. Let's take a look at the generated:

Initialize data

First, we need to create test data first. Here I write the data to a constant class Const, as shown below:

Public class Const {public interface OrderInfo {public static final String [] [] orderOne = new String [] [] {"123", "Kowloon", "13294352311 ", "guanshankou, Wuhan" },{ "124", "Jia", "13294352312", "Wuhan Fruit Lake" },{ "125", "Chen jia", "13294352315 ", "Wuhan Hashi" },{ "126", "Li", "13294352316", "Wuhan yangjiawan "}};}}

Theoretically, the data is read from the background.
This article simulates and prints the order information, so an order Model class is required here:

public class Order implements Serializable {    public String id;    public String restPhone;    public String restName;    public String receiverAddr;    public Order(String id,String restPhone, String restName, String receiverAddr) {        this.id = id;        this.restPhone = restPhone;        this.restName = restName;        this.receiverAddr = receiverAddr;    }}

Memory card

Next we need to determine whether the memory card exists and whether the memory is large enough. First obtain the memory size in the specified directory:

/** Get SD available capacity */private static long getAvailableStorage (Context context) {String root = context. getExternalFilesDir (null ). getPath (); StatFs statFs = new StatFs (root); long blockSize = statFs. getBlockSize (); long availableBlocks = statFs. getAvailableBlocks (); long availableSize = blockSize * availableBlocks; // Formatter. formatFileSize (context, availableSize); return availableSize ;}

The path used here is getExternalFilesDir, which specifies the directory SDCard/Android/data/your app package name/files/, which is used to store some data that has been stored for a long time, when an application is detached, It is deleted at the same time. In this case, the getExternalCacheDir method is used to store temporary files. Then, StatFs is used to calculate the available capacity.
Next, you can determine the memory before writing data to an excel file as follows:

If (! Environment. getExternalStorageState (). equals (Environment. MEDIA_MOUNTED) & getAvailableStorage ()> 1000000) {Toast. makeText (context, "SD card unavailable", Toast. LENGTH_LONG ). show (); return;} File file; File dir = new File (context. getExternalFilesDir (null ). getPath (); file = new File (dir, fileName + ". xls "); if (! Dir. exists () {dir. mkdirs ();}

If the memory card does not exist or the memory is less than 1 MB, do not write, and then create the corresponding folder and name it. Next, let's focus on how to write data into excel.

Generate related packages written into excel
Here we need to import the jxl package, which is mainly used to process excel. This package will be attached to the project in github and the link will be provided later. Generate an excel worksheet
The following code generates an excel table in the specified path. It is only an empty table.

WritableWorkbook wwb;        OutputStream os = new FileOutputStream(file);        wwb = Workbook.createWorkbook(os);

Add sheet table
Anyone familiar with excel knows that many sheet tables can be created in excel. The following code generates the first worksheet named "order ":

WritableSheet sheet = wwb. createSheet ("order", 0 );

Add an excel Header
Add an excel header. Here you can customize the Header style. First read the code:

String [] title = {"order", "store name", "phone", "Address"}; Label label; for (int I = 0; I <title. length; I ++) {// Label (x, y, z) indicates column x + 1 of the cell, row y + 1 of the cell, content z // specify the position of the cell and content Label = new label (I, 0, title [I], getHeader () in the sub-object of the Label object ()); // Add the defined cells to the sheet. addCell (label );}

I am writing the header information to death. A cell in the table corresponds to a Label. For example, label (, "a") indicates that the cell information in the first column of the First row is. GetHeader () is a custom style and returns a WritableCellFormat. To see how to customize the style:

Public static WritableCellFormat getHeader () {WritableFont font = new WritableFont (WritableFont. TIMES, 10, WritableFont. BOLD); // define the font try {font. setColour (Colour. BLUE); // BLUE font} catch (WriteException e1) {e1.printStackTrace ();} WritableCellFormat format = new WritableCellFormat (font); try {format. setAlignment (jxl. format. alignment. CENTRE); // format in the left-right corner. setVerticalAlignment (jxl. format. verticalAlignment. CENTRE); // The Upper and Lower center format. setBorder (Border. ALL, BorderLineStyle. THIN, Colour. BLACK); // BLACK border format. setBackground (Colour. YELLOW); // YELLOW background} catch (WriteException e) {e. printStackTrace ();} return format ;}

As you can see from the code above, you can get the WritableFont to customize some fonts, such as the color size, set the text box style through WritableCellFormat, and set the border background color. For more information, see the api documentation. Here we only provide examples.

Add excel content.

For (int I = 0; I <exportOrder. size (); I ++) {Order order = exportOrder. get (I); Label orderNum = new Label (0, I + 1, order. id); Label restaurant = new Label (1, I + 1, order. restName); Label nameLabel = new Label (2, I + 1, order. restPhone); Label address = new Label (3, I + 1, order. receiverAddr); sheet. addCell (orderNum); sheet. addCell (restaurant); sheet. addCell (nameLabel); sheet. addCell (address); Toast. makeText (context, "written successfully", Toast. LENGTH_LONG ). show ();}

This is no more simple, it is to get the order information and write it one by one.

Main class

The demo layout is very simple: click a button to generate an excel file, and it will not be pasted here. The MainActivity code is as follows:

protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        int length = Const.OrderInfo.orderOne.length;        for(int i = 0;i < length;i++){            Order order = new Order( Const.OrderInfo.orderOne[i][0],  Const.OrderInfo.orderOne[i][1],  Const.OrderInfo.orderOne[i][2],  Const.OrderInfo.orderOne[i][3]);            orders.add(order);        }        btn = (Button)super.findViewById(R.id.btn);        btn.setOnClickListener(new OnClickListener() {            @Override            public void onClick(View v) {                // TODO Auto-generated method stub                try {                    ExcelUtil.writeExcel(MainActivity.this,                            orders, "excel_"+new Date().toString());                } catch (Exception e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        });    }

Here, I encapsulate the excel Code generated above into a tool class ExcelUtil, which will be called directly later. MainActivity is to assemble the array into Order and call ExcelUtil to write. By now, android has implemented the excel function.