【澳门葡京备用网址】openpyxl处理excel2007文档思路以及心得,上传Excel文件使用JXL解析

背景:

  利用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)

 末了,等新的文书写好,直接用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可打印字符的限定替代,常见中文字符的范围是\【澳门葡京备用网址】openpyxl处理excel2007文档思路以及心得,上传Excel文件使用JXL解析。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属性一致,文件名称和文件类型前边要抬高文件域的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);  
        } 

回到的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

分开英文名就很简单了,空格分开,第一有的是名,第二有的是姓,其他情状暂时不管就行。

相关文章

发表评论

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

*
*
Website