我如何调整我的代码，使其兼容Microsoft Excel？ [英] How can I adapt my code to make it compatible to Microsoft Excel?

问题描述

问题

我试图实现一个网络API（基于 Flask em>），其将用于在给定一些特定条件的情况下查询数据库，重建数据，最后将结果导出到 .csv 文件

。 b
$ b

由于数据量真的很大，我不能构造整个数据集并生成 .csv / em>文件（例如，使用pandas创建一个DataFrame，最后调用df.to_csv（）），因为这会导致一个慢的查询，也许http连接会结束超时。

所以我创建一个生成器，每次查询数据库500条记录，并逐个产生结果，如：

  def __generator（q）：
 [...]＃一些代码在这里
而True：
如果记录==无：
 break 
 records = q [offset：offset + limit]＃q表示一个sqlalchemy查询对象
 [...]＃省略一些重建代码
 
记录在记录中：
 yield record 
  

，并向客户端发送 .csv ：

  __generate（q），mimetype ='text / csv'）＃Flask 
  

数据由'uft-8'编码，但当我尝试使用Microsoft Excel打开 .csv 文件时，

• p>向导出文件中添加 BOM 标头无效;




• 使用其他类似 gb18030'和'cp936'，大​​多数凌乱的代码消失了，有些仍然保留，表结构的某些部分变得很奇怪。

我的问题

我可以让我的代码与Microsoft Excel兼容吗？这意味着至少应该满足两个条件：

• 没有凌乱的代码，很好地显示;




• 结构良好的表格;

strong>我会非常感谢您的回答。 =http://www.hesa.ac.uk/submit-import =nofollow>如何将csv文件导入excel？您是否尝试将csv作为文本文件导入？

通过阅读每个列的文本格式，列，它读作不同的类型，如日期。您的代码可能是正确的，并且excel可能只是在将其解析为csv时修改数据 - 通过导入为文本格式，它不会修改任何内容。

Problem

I was trying to implement an web API(based on Flask), which would be used to query the database given some specific conditions, reconstruct the data and finally export the result to a .csv file.

Since the amount of data is really really huge, I can not construct the whole dataset and generate the .csv file all at once(e.g. create a DataFrame using pandas and finally call df.to_csv()), because that would cause a slow query and maybe the http connection would end up timeout.

So I create a generator which query the database 500 records per time and yield the result one by one, like:

def __generator(q):
    [...]    # some code here
    while True:
        if records == None:
            break
        records = q[offset:offset+limit] # q means a sqlalchemy query object
        [...]   # omit some reconstruct code

        for record in records:
            yield record

and finally construct a Response object, and send .csv to client side:

return Response(__generate(q), mimetype='text/csv')  # Flask

The generator works well and all data are encoded by 'uft-8', but when I try to open the .csv file using Microsoft Excel, it appears to be messy code.

Measures Already Tried

• add a BOM header to the export file, doesn't work;

• using some other encode like 'gb18030', and 'cp936', most of the messy code disappear, some still remained, and some part of the table structure become weird.

My Question Is

How can I make my code compatible to Microsoft Excel? That means at least two conditions should be satisfied:

• no messy code, well displayed;

• well structured table;

I would be really appreciated for your answer!

解决方案

• How are you importing the csv file to excel? Have you tried importing the csv as a text file?

By reading as text format for each column, it wont modify columns that it reads as different types like dates. Your code may be correct, and excel may just be modifying the data when it parses it as a csv - by importing as text format, it wont modify anything.

这篇关于我如何调整我的代码，使其兼容Microsoft Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！