【bf88必发唯一官网】openpyxl处理excel2007文档思路以及心得,上传Excel文件使用JXL解析

by admin on 2019年2月12日

背景:

  利用phpspreadsheet可以轻松的解析excel文件,然则phpspreadsheet的内存消耗也是比较大的,我试过解析将近5M的纯文字excel内存使用量就会超越php暗许的最大内存128M。
  当然这足以用调节内存大小的法子来缓解,不过在并发量大的时候就相比危急了。所以明天介绍下第三种方法,利用phpspreadsheet对excel文件进行切割,那是个拿时间换空间的法门所以一般对时效性须求低的急需可以选择。

  继续明日的说,前天说到用fullcalendar日期控件来突显日程,将来有一个需要是读取Excel中的日程音讯,然后呈现在日历上,继续记录备忘。

用python + openpyxl处理excel2007文档思路以及心得,

找寻工具

确定职分之后第一步就是找个趁手的库来办事。 Python
Excel上列出了xlrd、xlwt、xlutils那多少个包,不过

它们都相比较老,xlwt甚至不协理07版之后的excel
它们的文档不太友好,都大概要求去读源代码,而老姐的职责相比较紧,加上我当下在晚期,没有这一个时刻细读源代码
再一番招来后我找到了openpyxl,支持07+的excel,向来有人在保证,文档清晰易读,参照Tutorial和API文档很快就能上手,就是它了~

安装

以此很简单,直接pip install openpyxl,呵呵呵~

因为自身不需求处理图片,就从未有过装pillow。

局地设想

源文件大致一个在1~2MB左右,相比较小,所以可以直接读入内存处理。
既是是处理excel,何况他们所有组明确都以win下办事(数据都用excel存了=
=,商科的人呀……),这一个剧本依旧在win下做啊
其一义务完全不必要我对现有的公文做修改!囧……我假诺读入、处理、再写出另一个文书就行了

上学使用

嗯,就是开辟cmd,然后用python的shell各个玩那些模块来上手……(win下并未装ipython,囧)

做这一个小本子基本上我只要求import多少个东西

from openpyxl import Workbook
from openpyxl import load_workbook

load_workbook顾名思义是把公文导入到内存,Workbook是最中央的一个类,用来在内存里创造文件最终写进磁盘的。

干活

率先我须要导入这几个文件

inwb = load_workbook(filename)

收获的就是一个workbook对象

接下来我索要成立一个新的文本

outwb = Workbook()

随之在那些新文件里,用create_sheet新建多少个工作表,比如

careerSheet = outwb.create_sheet(0, 'career')

就会从头顶插入一个叫career的工作表(约等于说用法类似python
list的insert)

接下去本身索要遍历输入文件的种种工作表,并且依据表名做一些工作(e.g.倘诺表名不是数字,我不必要处理),openpyxl帮忙用字典一样的方法经过表名获取工作表,获取一个工作簿的表名的法子是get_sheet_names

for sheetName in inwb.get_sheet_names():
  if not sheetName.isdigit():
    continue
  sheet = inwb[sheetName]

获取工作表之后,就是按列和行处理了。openpyxl会基于办事表里实际有数量的区域来规定行数和列数,获取行和列的不二法门是sheet.rows和sheet.columns,它们都得以像list一样用。比如,假设自个儿想跳过数码少于2列的表,可以写

if len(sheet.columns) < 2:
  continue

一旦本身想拿到这些工作表的前两列,可以写

colA, colB = sheet.columns[:2]

除开用columns和rows来赢得那个工作表的队列之外,还足以用excel的单元格编码来获取一个区域,比如

cells = sheet['A1':'B20']

有点像excel自身的函数,可以拉出一块二维的区域~

为了便于处理,遇到一个从未C列的工作表,我要创立一个和A列等长的空的C列出来,那么自个儿可以用sheet.cell那个措施,通过传播单元格编号和丰裕空值来成立新列。

alen = len(colA)
for i in range(1, alen + 1):
  sheet.cell('C%s' % (i)).value = None

注意:excel的单元格命名是从1初始的~

上边的代码也显得出来了,获取单元格的值是用cell.value(可以是左值也可以是右值),它的花色可以是字符串、浮点数、整数、或者时间(datetime.datetime),excel文件里也会扭转对应项目标多寡。

得到逐个单元格的值之后,就可以展开操作了~openpyxl会自
动将字符串用unicode编码,所以字符串都以unicode类型的。

除开逐一各种单元格用cell.value修改值以外,还能一行行append到工作表里

sheet.append(strA, dateB, numC)

【bf88必发唯一官网】openpyxl处理excel2007文档思路以及心得,上传Excel文件使用JXL解析。 最终,等新的文书写好,直接用workbook.save保存就行

outwb.save("test.xlsx")

其一会覆盖当前已有的文件,甚至你在此以前读取到内存的不得了文件。

有些要小心的地点
只要要在遍历一列的各类单元格的时候得到当前单元格的在那个column对象里的下标

for idx, cell in enumerate(colA):
  # do something...

为了避防万一获取的数码两端有看不见的空格(excel文件里很宽泛的坑),记得strip()

如若工作表里的单元格没有数据,openpyxl会让它的值为None,所以假设要按照单元格的值做处理,无法事先假定它的项目,最好用

if not cell.value
  continue

等等的语句来先行判断

假诺要处理的excel文件里有好多noise,比如当你预期一个单元格是岁月的时候,有些表的数据大概是字符串,这时候可以用

if isinstance(cell.value, unicode):
  break

等等的讲话处理。

【bf88必发唯一官网】openpyxl处理excel2007文档思路以及心得,上传Excel文件使用JXL解析。win下的cmd如同不太好设定用utf-8的code
page,假若是简体闽南语的话能够用936(GBK),print的时候会自动从unicode转换来GBK输出到终极。
 

一对帮衬处理中文难题的小函数
自我处理的表有一些压倒GBK范围的字符,当我索要把部分音讯print出来监控处理速度的时候尤其辛劳,好在它们都以可以漠视的,我直接用空格替换再print也行,所以加上部分自己当然就要替换掉的分隔符,我得以:

# annoying seperators
dot = u'\u00b7'
dash = u'\u2014'
emph = u'\u2022'
dot2 = u'\u2027'

seps = (u'.', dot, dash, emph, dot2)

def get_clean_ch_string(chstring):
  """Remove annoying seperators from the Chinese string.

  Usage:
    cleanstring = get_clean_ch_string(chstring)
  """
  cleanstring = chstring
  for sep in seps:
    cleanstring = cleanstring.replace(sep, u' ')
  return cleanstring

此外我还有一个需要,是把英文名[空格]华语名分成英文姓、英文名、中文姓、中文名。

先是自身须要能把英文和中文分割开,我的艺术是用正则匹配,根据广泛中国和英国文字符在unicode的限定来套。匹配英文和中文的正则pattern如下:

# regex pattern matching all ascii characters
asciiPattern = ur'[%s]+' % ''.join(chr(i) for i in range(32, 127))
# regex pattern matching all common Chinese characters and seporators
chinesePattern = ur'[\u4e00-\u9fff. %s]+' % (''.join(seps))

英文就用ASCII可打印字符的限量替代,常见中文字符的限定是\u4e00-\u9fff,那么些seps是前方提到过的过量GBK范围的局部字符。
除了简便易行的分开,我还必要处理只有中文名从未英文名、唯有英文名没有汉语名等气象,判断逻辑如下:

def split_name(name):
  """Split [English name, Chinese name].

    If one of them is missing, None will be returned instead.
  Usage:
    engName, chName = split_name(name)
  """
  matches = re.match('(%s) (%s)' % (asciiPattern, chinesePattern), name)
  if matches: # English name + Chinese name
    return matches.group(1).strip(), matches.group(2).strip()
  else:
    matches = re.findall('(%s)' % (chinesePattern), name)
    matches = ''.join(matches).strip()
    if matches: # Chinese name only
      return None, matches
    else: # English name only
      matches = re.findall('(%s)' % (asciiPattern), name)
      return ''.join(matches).strip(), None

得到了汉语名之后,我急需分割成姓和名,因为义务要求不须要把姓名分割得很肯定,我就根据广泛的国语名姓名分割形式来分——五个字or多个字的首先个字是姓,七个字的前多个字是姓,名字带分隔符的(少数民族名字)分隔符前是姓(那里运用了前头的get_clean_ch_string函数来移除分隔符),名字再长一些又不带分割符的,若是任何字符串都以名字。(注意波兰语的first
name 指的是名,last name指的是姓,2333)

def split_ch_name(chName):
  """Split the Chinese name into first name and last name.

    * If the name is XY or XYZ, X will be returned as the last name.
    * If the name is WXYZ, WX will be returned as the last name.
    * If the name is ...WXYZ, the whole name will be returned
     as the last name.
    * If the name is ..ABC * XYZ..., the part before the seperator
     will be returned as the last name.
  Usage:
    chFirstName, chLastName = split_ch_name(chName)
  """
  if len(chName) < 4: # XY or XYZ
    chLastName = chName[0]
    chFirstName = chName[1:]
  elif len(chName) == 4: # WXYZ
    chLastName = chName[:2]
    chFirstName = chName[2:]
  else: # longer
    cleanName = get_clean_ch_string(chName)
    nameParts = cleanName.split()
    print u' '.join(nameParts)
    if len(nameParts) < 2: # ...WXYZ
      return None, nameParts[0]
    chLastName, chFirstName = nameParts[:2] # ..ABC * XYZ...
  return chFirstName, chLastName

分开英文名就很简单了,空格分开,第一有的是名,第二有的是姓,别的意况临时不管就行。

寻找工具

方法:

  先放个phpspreadsheet官网提供的一个功力readCell,大家就可以运用那么些功用来拓展切割。

  首先对excel文件举行预读,重如果取得具有的工作表以及工作表下边的数目行数,那个阶段readCell方法平昔重回的都是false,大家只必要记录readCell进来的工作表及数量行数。

  然后就是对取得到的记录进行分析,确定每部分数据必要装多少行原始excel的数额,必要小心的是为了幸免内容混淆,不要讲多个工作表的内容切到一起。

  最后就是循环分析的数码和再度利用readCell获取每部分数据,注意每趟读取文件后都要使用disconnectWorksheets主意清理phpspreadsheet的内存。

  经过我自身的测试发现,利用该方法分析5M的excel文件,平均只须要21M的内存就可以消除!

一、上传文件

  上传文件也思疑了自己很久,后天同步记录一下。项目框架是SSH的,所以上传文件就采取了struts2的fileupload,所要求的jar包都在引入struts2的时候引入了,然后就是直接上代码操作了。

规定义务之后第一步就是找个趁手的库来行事。 Python
Excel上列出了xlrd、xlwt、xlutils那多少个包,但是

代码

  

1.1 页面

<form id="excelform" action="。。。。。。。。。" method="post" enctype="multipart/form-data">
            <div class='excel-btn'>
                <!-- File upload field -->
                <div class="input-group input-group-sm">
                    <input id="source" type="file" name="excel" class="form-control" accept=".xlsx,.xls"/>

                        <button id="import" type="submit" class="btn btn-primary">
                            上传&nbsp;<i class="glyphicon glyphicon-upload"></i>
                        </button>

                </div>
                <!-- export agenda event field -->
                <div class="text-right" style="padding-top:10px">
                    <div class="btn-group btn-group-sm">
                        <button id="export" type="button" class="btn btn-warning">
                            导出日程&nbsp;<i class="glyphicon glyphicon-share"></i>
                        </button>
                        <a class="btn btn-success" href="/wldproject/model/events.xls">
                            模版下载&nbsp;<i class="glyphicon glyphicon-download"></i>
                        </a>
                    </div>
                </div>
            </div>
        </form>

上传文件的form必不可少的就是enctype=”multipart/form-data”,文件域中的accept=”.xlsx,.xls”表示接受上传的文件类型,当然也可以在struts2的拦截器里面安装上传文件的尺寸、上传文件的项目等音信,我那里运用的是另一种形式:

<!-- 指定允许上传的文件最大字节数。默认值是2097152(2M) -->
    <constant name="struts.multipart.maxSize" value="1048576"/>
    <!-- 设置上传文件的临时文件夹,默认使用javax.servlet.context.tempdir -->
    <constant name="struts.multipart.saveDir " value="d:/tmp" />

文本吸纳类型在文件域中安装,允许上传文件的深浅在struts2的配备文件中平素动用constant设置了,上边我标红的代码要留心一下,上次本身在地点设置的D盘,不过放到服务器上的时候,服务器唯有C盘没有D盘,然后就一贯报错,害的自个儿折腾了绵绵才看出来,这一个是用来囤积上传文件的临时文件夹。

+
openpyxl处理excel2007文档思路以及心得, 寻觅工具
确定职责之后第一步就是找个趁手的库来干活。 Python
Excel上列出了xlrd、xlwt、xl…

它们都比较老,xlwt甚至不辅助07版之后的excel
它们的文档不太对劲儿,都大概须要去读源代码,而老姐的义务相比较紧,加上本身立马在晚期,没有这几个日子细读源代码
再一番摸索后本身找到了openpyxl,援救07+的excel,一向有人在维护,文档清晰易读,参照Tutorial和API文档很快就能上手,就是它了~

1.2 JS提交表单

行使ajaxForm的法门交给表单,因而要引入jquery和jquery 
form的js文件

//提交表单
    $("#excelform").ajaxForm({
        beforeSubmit: showRequest, //表单验证
        success: showResponse //成功返回
    });

function showRequest(){
        var filename = $("#source").val();
        if(filename == null || filename == ''){
            alert("请选择文件...");
            $("#source").focus();
            return false;
        }
        $("#excelform").attr("action", "。。。。。。");
    }

    function showResponse(responseText, statusText){
        if(statusText=="success") {    
            if(responseText == "1") {
                alert("Excel文件导入成功");
                //重新获取所有事件数据
                $('#calendar').fullCalendar('refetchEvents');
            } else {
                alert(responseText);
            }
        } else {
            alert(statusText);
        }
    }

安装

1.3 后台完结

private File excel;
    private String excelContentType;
    private String excelFileName;

fileupload上传文件时,先接到上边的多个参数,File
的称呼要跟文件域的name属性一致,文件名称和文件类型前面要添Gavin件域的name属性。

public String importEvent() throws IOException {
        // 获取文件存储路径  
        // get the path to save the file
        String path = ServletActionContext.getRequest().getRealPath("/WEB-INF/upload");

        path += FileUtil.getPath();// child path
        // 获取文件存储名称
        // get the name save to
        String name = FileUtil.getName(excelFileName);
        // upload the file and return the target file object
        File file = FileUtil.upload(excel, path, name);

  在获取文件存储路径那里,我更欣赏使用String path =
request.getSession().getServletContext().getRealPath(“/WEB-INF/upload”);因为ServletActionContext.getRequest().getRealPath(“/WEB-INF/upload”);以往早已不引进应用了。

  为了读取时有利于,因而当天上传的文本放在upload文件夹上边的以当天日子命名的公文夹中,为了防止再次,以目今天期时间对当下文件举办重命名。

public static String getPath(){
        Date date = new Date();
        sdf.applyPattern("/yyyy-MM-dd");
        return sdf.format(date);
    }

public static String getName(String fileName){
        Date date = new Date();
        sdf.applyPattern("HH-mm-ss");
        return sdf.format(date) + getSuffix(fileName);
    }
    /**
     * @param fileName
     * @return
     */
    public static String getSuffix(String fileName){
        int dotIndex = fileName.lastIndexOf('.');
        return fileName.substring(dotIndex);
    }

  因为根本目标是分析上传的Excel文件,由此,上传文件之后回到该文件举行解析,具体上传步骤:

/**
     * @param source
     * @param dest
     * @return 
     */
    public static File upload(File src, String path, String name) {
        File directory = new File(path);
        if(!directory.exists()){
            directory.mkdirs();
        }
        File dest = new File(path, name);
        if(upload(src, dest)){
            return dest;
        }
        return null;
    }

/**
     * @param src
     * @param dest
     */
    public static boolean upload(File src, File dest) {

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        byte[] buf = new byte[1024];
        int len = 0;
        try {
            bis = new BufferedInputStream(new FileInputStream(src));
            bos = new BufferedOutputStream(new FileOutputStream(dest));
            while (((len = bis.read(buf)) != -1)) {
                bos.write(buf, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (bos != null) {
                    bos.close();
                }
                if (bis != null) {
                    bis.close();
                }
            } catch (Exception e) {
                bos = null;
                bis = null;
            }
        }
        return true;
    }

地点就是文本上传的进程,上传成功后对该文件举行分析,解析Excel使用的JXL的艺术,也可以应用POI的艺术分析。

那几个很不难,直接pip install openpyxl,呵呵呵~

二、JXL解析EXCEL

public boolean importEvent(File file) {

        ExcelUtil excel = new ExcelUtil();
        ExcelContent eContent = excel.getFromExcel(file);
        if(eContent == null){
            return false;
        }
        List<Agenda> alist = agendaDAO.getFromExcelContent(eContent);
        return agendaDAO.batchSave(alist);
    }

EXCEL表格可以明白为一个二维数组,因而使用List套List的点子来囤积读取出来的故事情节;

public class ExcelContent {

    private List<String> title;//标题
    private List<List<String>> content;//内容

从excel文件中读取音信囤积到ExcelContent
中:

/**
     * get contents from a excel file
     * @param file----the excel file path
     * @param hasTitle
     * @return
     */
    public ExcelContent getFromExcel(File file) {
        Workbook rwb = null;
        ExcelContent eContent = new ExcelContent();
        List<List<String>> datas = new ArrayList<List<String>>();
        try {
            rwb = Workbook.getWorkbook(file);
            Sheet sheet = rwb.getSheet(0);// deploy the first sheet
            int rows = sheet.getRows();
            // start to loop and get the datas
            for (int index = 0; index < rows; index++) {
                Cell[] cells = sheet.getRow(index);
                List<String> row = new ArrayList<String>();
                for (Cell cell : cells) {
                    row.add(getContent(cell));
                }
                if(index == 0){// title banner
                    eContent.setTitle(row);
                } else {
                    datas.add(row);
                }
            }
            eContent.setContent(datas);
        } catch (Exception e) {
            return null;
        }
        return eContent;
    }

率先必要创设一个workbook对象,约等于工作薄,可以是一个文书,也得以是一个输入流,

InputStream is = new FileInputStream(sourcefile);  

Workbook rwb = Workbook.getWorkbook(is); 

  获取到工作薄之后就是取得到办事表了,也就是sheet,那里唯有一个工作表,所以使用了Sheet sheet =
rwb.getSheet(0);倘诺一个行事薄里面有三个工作表,那么可以动用

Sheet[] sheets = rwb.getSheets();然后循环对各种sheet进行操作即可,int
sheets = rwb.getNumberOfSheets();可以博拿到sheet的数目。

  获取到sheet之后就足以对一个工作表举行操作了,int rows =
sheet.getRows();表示收获到该sheet中的行数,int rsColumns =
rs.getColumns();表示收获到总列数;

接头总行数之后循环取出每一行的多少
Cell[] cells =
sheet.getRow(index);表示取出第index行的数量,取多少的时候,由于EXCEL表格中留存日期格式的,因而要对数码举行简易的拍卖:

/**
     * excel format
     * @param cell
     * @return
     */
    private String getContent(Cell cell){
        CellType type = cell.getType();
        if(type == CellType.DATE){
            DateCell c = (DateCell) cell;
            return sdf.format(c.getDate());
        }
        return cell.getContents();
    }

取出的首先行数据为标题,前面的为正式的数目,假若没有标题,那就不需求处理标题了。取出excel中的数据后,将其坐落完成准备好的eContent 对象中回到,之后再从eContent 取出数据,存入数据库。

/** 
     * attention: no id include!!!
     * convert the Excel content to agenda objects without id included
     * @param eContent----the Excel content
     * @return a list of agenda objects
     */
    public List<Agenda> getFromExcelContent(ExcelContent eContent){

        List<String> title = eContent.getTitle();// excel title
        List<List<String>> contents = eContent.getContent();// excel rows

        List<Agenda> aList = new ArrayList<Agenda>();

        int len = title.size();
        // loop the all excel content
        for(List<String> row : contents){
            Agenda agenda = new Agenda();
            for(int i = 0; i < len; i++){
                String cell = row.get(i);
                String field = title.get(i);
                if(field.equalsIgnoreCase("title")){// title field
                    agenda.setTitle(cell.trim());
                } else if(field.equalsIgnoreCase("allday")){// all day field
                    if(cell.matches("[yY1]")){
                        agenda.setAllDay(true);
                    } else if(cell.matches("[nN0]")){
                        agenda.setAllDay(false);
                    }
                } else if(field.equalsIgnoreCase("starttime")){// start time field
                    if(!StringUtil.isSpace(cell)){
                        agenda.setStart(DateUtil.parse2Date(cell, format));
                    }
                } else if(field.equalsIgnoreCase("endtime")){// end time field
                    if(!StringUtil.isSpace(cell)){
                        agenda.setEnd(DateUtil.parse2Date(cell, format));
                    }
                } else if(field.equalsIgnoreCase("color")){// color field
                    agenda.setColor(cell.trim());
                } else if(field.equalsIgnoreCase("user")){// user field
                    agenda.setUser(cell.trim());
                } else if(field.equalsIgnoreCase("supporter")){// supporter field
                    agenda.setSupporter(cell.trim());
                }
            }
            aList.add(agenda);
        }
        return aList;
    }

这里面唯一要说的就是starttime和endtime,在excel文件中,那八个数值为时间戳,由此到这边之后须要对时间戳举办拍卖,转换成时间之后才能存入数据库;

 public static Date timeStamp2Date(String seconds,String format) {  
            if(seconds == null || seconds.isEmpty() || seconds.equals("null")){  
                return null;  
            }  
            if(format == null || format.isEmpty()) format = "yyyy-MM-dd HH:mm:ss";  
            SimpleDateFormat sdf = new SimpleDateFormat(format);  

            String str = sdf.format(new Date(Long.valueOf(seconds+"000")));
            return parse2Date(str,format);  
        } 

bf88必发唯一官网,回去的alist在持久化层举办批量仓储即可,那样读取EXCEL就做到了。

因为我不必要处理图片,就从未装pillow。

三、导出EXCEL

  页面在上传文件的时候已经提交了,导出启程就是查询数据库的日程,然后导出为一个excel文件即可。

一些设想

3.1 JS实现

//export agenda
    $("#export").click(function(){
        $("#excelform").attr("action", "。。。。。。");
        document.forms[0].submit();
    });

将方面上传的form的action改成导出的action,提交表单即可。

源文件大概一个在1~2MB左右,比较小,所以能够直接读入内存处理。
既是是处理excel,何况他们任何组明确都以win下办事(数据都用excel存了=
=,商科的人啊……),那个剧本依旧在win下做呢
这一个职责完全不须要我对现有的文书做修改!囧……我一旦读入、处理、再写出另一个文书就行了

3.2 后台完结

/**
     * @return
     * @throws IOException
     */
    public String exportEvent() throws IOException {
        // start to output
        response.addHeader("Content-Disposition", "attachment;filename=events.xls");
        response.setContentType("application/octet-stream");
        ServletOutputStream ss = response.getOutputStream();
        OutputStream stream = new BufferedOutputStream(ss);
        boolean success = excelServ.exportEvent(stream);
        if(!success){
            response.reset();
            response.setContentType("text/plain");
            PrintWriter out = response.getWriter();
            out.print("failed");
            out.flush();
            out.close();
        }
        return null;
    }

  response.addHeader(“Content-Disposition”,
“attachment;filename=events.xls”);
response.setContentType(“application/octet-stream”);那两句是用户点击下载按钮时,可以弹出指示框用户能够拔取直接打开依然下载,牵扯到http协议的有的事物,我也不是太懂,只精晓是那般写,不过太现实的自个儿就不知晓了,大家感兴趣的能够友善打听一下。

  下载安装好之后就是读取数据库数据,转换成excel格式了。

/**
     *  export events to Excel file
     * @return
     */
    public boolean exportEvent(OutputStream os) {
        List<Agenda> alist = agendaDAO.findAll();
        if(alist == null || alist.size() == 0){
            return false;
        }
        List<List<String>> content = new ArrayList<List<String>>();
        for(Agenda agenda : alist){
            // add the agenda property to a String row
            List<String> row = new ArrayList<String>();
            row = agenda.toListString();
            content.add(row);
        }
        ExcelUtil excel = new ExcelUtil();
        excel.exportToExcel(os, Agenda.getPrintHead(), content);
        return true;
    }

  前边说过excel数据就是一个二维数组,由此,可以先将查询出的日程列表举行拍卖,转换成List<List<String>>格局,为了贯彻那种效应,我在agenda中添加了toListString()方法:

 

/**
     * used to convert the Agenda object to String list
     * @return list of string array stands for every filed
     */
    public List<String> toListString(){
        // add the agenda property to a String row
        List<String> row = new ArrayList<String>();
        row.add(String.valueOf(id));
        row.add(title);

        String format = "yyyy-MM-dd";
        if(!allDay){
            format = "yyyy-MM-dd HH:mm";
        }
        row.add(DateUtil.parse2String(start, format));
        row.add(DateUtil.parse2String(end, format));
        row.add(StringUtil.bool2String(allDay));
        row.add(color);
        row.add(this.user + " ");
        row.add(this.supporter + " ");
        return row;
    }

 

归来一个String类型的list集合,添加到content中,之后再得到到要导出的数额的标题,也在agenda中达成:

/**
     * @return the String array used to export the agenda object to excel
     */
    public static String[] getPrintHead(){
        return new String[]{"ID", "title", "starttime", "endtime", "allday", "color", "user", "supporter"};
    }

那八个处理已毕未来,再加上输出流即可开头导出excel文件:

/**
     * export to excel
     * @param os----the output stream of excel file to save
     * @param title----the array of the title banner
     * @param content----a array list of the data to save
     * @return
     */
    public void exportToExcel(OutputStream os, String[] title, List<List<String>> content) {
        WritableWorkbook workbook = null;//create the excel
        WritableSheet sheet = null;//create excel sheet
        // start
        try {
            workbook = Workbook.createWorkbook(os);
            sheet = workbook.createSheet("sheet1", 0);

            int rowNum = 0;
            // whether the title include in the source file
            if (title != null && title.length != 0) {
                /********** format the excel cell *************/
                WritableCellFormat title_style = cellFormat.getCellFormat(ExcelCellFormat.TITLE_CENTER);
                for (int i = 0; i < title.length; i++) {
                    sheet.addCell(new Label(i, 0, title[i], title_style));
                }
                rowNum++;
            }
            WritableCellFormat text_style = cellFormat.getCellFormat(ExcelCellFormat.TEXT_LEFT);
            for (List<String> rows : content) {
                int colNum = 0;
                for (String obj : rows) {
                    if (obj == null) {
                        obj = "";
                    }
                    Label la = new Label(colNum, rowNum, obj,text_style);
                    sheet.addCell(la);
                    colNum++;
                }
                rowNum++;
            }
            workbook.write();// write the content to the file stream
        } catch (Exception e) {
            e.printStackTrace();
        } finally {// close
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

  与读取excel文件类似,首先要使用workbook类的工厂方法创立一个可写入的工作薄,这里要留心的是,只好通过
API提供的工厂方法来创立Workbook,而不能应用WritableWorkbook的构造函数,因为类WritableWorkbook的布局函
数为protected类型。

  创制可写入的做事薄有三种方式,一种是file:

   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile)); 

  一种是输出流:

  1.  OutputStream os = new FileOutputStream(targetfile); 
  2.     jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

 

 

正文使用输出流的办法展开导出,工作薄成立完毕将来就须要创设工作表,使用sheet
= workbook.createSheet(“sheet1”,
0);创造工作表,七个参数分别代表工作表的称谓和劳作表在干活薄中的地点。

工作薄和工作表设置好之后就是对故事情节开展安装了,jxl提供对单元格及其单元格中的内容展开设置的办法,比如设置字体、设置字体颜色等等。

public class ExcelCellFormat {

    public static int TITLE_CENTER = 0;
    public static int TEXT_LEFT = 1;
    public static int CELLFORMATE_TEXT_RIGHT = 2;

    public WritableCellFormat getCellFormat(int type) throws WriteException {
        WritableCellFormat cellFormat = null;
        if (TITLE_CENTER == type) {// 用于标题居中
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
            cellFormat = new WritableCellFormat(BoldFont);
            cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 用于文字垂直
            cellFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        } else if (TEXT_LEFT == type) {// 用于正文居左
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            cellFormat = new WritableCellFormat(NormalFont);
            cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            cellFormat.setAlignment(Alignment.LEFT); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        } else if (CELLFORMATE_TEXT_RIGHT == type) {// 用于正文居左
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            cellFormat = new WritableCellFormat(NormalFont);
            cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            cellFormat.setAlignment(Alignment.RIGHT); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        }
        return cellFormat;
    }
}

地点的情节表示对单元格中内容的片段装置,其他的设置我们可以查查API就可以了。

 

  上边就是使用JXL举办excel的导入导出的全体内容了,有说的歇斯底里的地点欢迎我们指正。

 

学习使用

嗯,就是开辟cmd,然后用python的shell各类玩这一个模块来上手……(win下并未装ipython,囧)

做这几个小本子基本上我只要求import几个东西

from openpyxl import Workbook
from openpyxl import load_workbook

load_workbook顾名思义是把公文导入到内存,Workbook是最基本的一个类,用来在内存里创造文件最终写进磁盘的。

干活

第一我必要导入这些文件

inwb = load_workbook(filename)

收获的就是一个workbook对象

下一场我索要创设一个新的公文

outwb = Workbook()

随之在这一个新文件里,用create_sheet新建多少个工作表,比如

careerSheet = outwb.create_sheet(0, 'career')

就会从头顶插入一个叫career的工作表(也等于说用法类似python
list的insert)

接下去自个儿索要遍历输入文件的每种工作表,并且根据表名做一些工作(e.g.假诺表名不是数字,我不必要处理),openpyxl协助用字典一样的法门经过表名获取工作表,获取一个工作簿的表名的章程是get_sheet_names

for sheetName in inwb.get_sheet_names():
  if not sheetName.isdigit():
    continue
  sheet = inwb[sheetName]

收获工作表之后,就是按列和行处理了。openpyxl会依照工作表里实际有数量的区域来确定行数和列数,获取行和列的办法是sheet.rows和sheet.columns,它们都足以像list一样用。比如,如若自己想跳过数码少于2列的表,可以写

if len(sheet.columns) < 2:
  continue

一旦本人想博得这一个工作表的前两列,可以写

colA, colB = sheet.columns[:2]

除开用columns和rows来赢得那么些工作表的连串之外,还是可以用excel的单元格编码来获取一个区域,比如

cells = sheet['A1':'B20']

有点像excel自个儿的函数,能够拉出一块二维的区域~

为了方便处理,蒙受一个未曾C列的工作表,我要创立一个和A列等长的空的C列出来,那么我得以用sheet.cell这些主意,通过传播单元格编号和丰裕空值来创造新列。

alen = len(colA)
for i in range(1, alen + 1):
  sheet.cell('C%s' % (i)).value = None

只顾:excel的单元格命名是从1先河的~

地方的代码也展现出来了,获取单元格的值是用cell.value(可以是左值也得以是右值),它的项目可以是字符串、浮点数、整数、可能时间(datetime.datetime),excel文件里也会转移对应品种的多寡。

得到各种单元格的值之后,就足以举行操作了~openpyxl会自
动将字符串用unicode编码,所以字符串都以unicode类型的。

除了逐一每一个单元格用cell.value修改值以外,还是能一行行append到工作表里

sheet.append(strA, dateB, numC)

终极,等新的文书写好,直接用workbook.save保存就行

outwb.save("test.xlsx")

那一个会覆盖当前已部分文件,甚至你后面读取到内存的尤其文件。

一部分要专注的地点
万一要在遍历一列的逐个单元格的时候取得当前单元格的在那个column对象里的下标

for idx, cell in enumerate(colA):
  # do something...

为了防范获取的多少两端有看不见的空格(excel文件里很宽泛的坑),记得strip()

假若工作表里的单元格没有数量,openpyxl会让它的值为None,所以假若要根据单元格的值做拍卖,不可能事先假定它的项目,最好用

if not cell.value
  continue

等等的语句来先行判断

一旦要拍卖的excel文件里有不少noise,比如当您预期一个单元格是时刻的时候,有些表的多寡可能是字符串,这时候可以用

if isinstance(cell.value, unicode):
  break

等等的言辞处理。

win下的cmd似乎不太好设定用utf-8的code
page,假如是简体中文的话可以用936(GBK),print的时候会自动从unicode转换来GBK输出到顶点。

有的增援处理汉语难题的小函数
本身处理的表有一些当先GBK范围的字符,当我须求把一些信息print出来监控处理速度的时候分外坚苦,好在它们都以足以无视的,我直接用空格替换再print也行,所以加上一些自己自然就要替换掉的分隔符,我可以:

# annoying seperators
dot = u'\u00b7'
dash = u'\u2014'
emph = u'\u2022'
dot2 = u'\u2027'

seps = (u'.', dot, dash, emph, dot2)

def get_clean_ch_string(chstring):
  """Remove annoying seperators from the Chinese string.

  Usage:
    cleanstring = get_clean_ch_string(chstring)
  """
  cleanstring = chstring
  for sep in seps:
    cleanstring = cleanstring.replace(sep, u' ')
  return cleanstring

除此以外我还有一个急需,是把英文名[空格]华语名分成英文姓、英文名、普通话姓、中文名。

率先本身须要能把英文和中文分割开,我的方法是用正则匹配,依据广泛中国和英国文字符在unicode的限制来套。匹配英文和华语的正则pattern如下:

# regex pattern matching all ascii characters
asciiPattern = ur'[%s]+' % ''.join(chr(i) for i in range(32, 127))
# regex pattern matching all common Chinese characters and seporators
chinesePattern = ur'[\u4e00-\u9fff. %s]+' % (''.join(seps))

英文就用ASCII可打印字符的限制替代,常见汉语字符的限制是\u4e00-\u9fff,那些seps是目前提到过的胜出GBK范围的一对字符。
除了简易的划分,我还索要处理唯有汉语名尚未英文名、唯有英文名没有汉语名等景况,判断逻辑如下:

def split_name(name):
  """Split [English name, Chinese name].

    If one of them is missing, None will be returned instead.
  Usage:
    engName, chName = split_name(name)
  """
  matches = re.match('(%s) (%s)' % (asciiPattern, chinesePattern), name)
  if matches: # English name + Chinese name
    return matches.group(1).strip(), matches.group(2).strip()
  else:
    matches = re.findall('(%s)' % (chinesePattern), name)
    matches = ''.join(matches).strip()
    if matches: # Chinese name only
      return None, matches
    else: # English name only
      matches = re.findall('(%s)' % (asciiPattern), name)
      return ''.join(matches).strip(), None

得到了中文名之后,我须要分割成姓和名,因为义务需求不需求把姓名分割得很鲜明,我就根据广泛的国语名姓名分割格局来分——七个字or五个字的率先个字是姓,三个字的前五个字是姓,名字带分隔符的(少数民族名字)分隔符前是姓(那里运用了前头的get_clean_ch_string函数来移除分隔符),名字再长一些又不带分割符的,假若任何字符串都是名字。(注意马耳他语的first
name 指的是名,last name指的是姓,2333)

def split_ch_name(chName):
  """Split the Chinese name into first name and last name.

    * If the name is XY or XYZ, X will be returned as the last name.
    * If the name is WXYZ, WX will be returned as the last name.
    * If the name is ...WXYZ, the whole name will be returned
     as the last name.
    * If the name is ..ABC * XYZ..., the part before the seperator
     will be returned as the last name.
  Usage:
    chFirstName, chLastName = split_ch_name(chName)
  """
  if len(chName) < 4: # XY or XYZ
    chLastName = chName[0]
    chFirstName = chName[1:]
  elif len(chName) == 4: # WXYZ
    chLastName = chName[:2]
    chFirstName = chName[2:]
  else: # longer
    cleanName = get_clean_ch_string(chName)
    nameParts = cleanName.split()
    print u' '.join(nameParts)
    if len(nameParts) < 2: # ...WXYZ
      return None, nameParts[0]
    chLastName, chFirstName = nameParts[:2] # ..ABC * XYZ...
  return chFirstName, chLastName

分开英文名就很粗略了,空格分开,第一有的是名,第二局地是姓,其余情形临时不管就行。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图