本文目录
有什么软件可以随意提取Excel表格中的数据
推荐你使用Java进行开发，通过Apache POI开源项目实现对Excel表格的数据读取：
下载POI4.1.0:
实现代码：
public class ExcelOperate {
public static void main(String args) throws Exception {
File file = new File("ExcelDemo.xls");
String result = getData(file, 1);
int rowLength = result.length;
for(int i=0;i《rowLength;i++) {
for(int j=0;j《result.length;j++) {
System.out.print(result+"\t\t");
}
System.out.println();
}
}
/**
* 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
* @param file 读取数据的源Excel
* @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
* @return 读出的Excel中数据的内容
* @throws FileNotFoundException
* @throws IOException
*/
public static String getData(File file, int ignoreRows)
throws FileNotFoundException, IOException {
List《String》 result = new ArrayList《String》();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex 《 wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex);
// 第一行为标题，不取
for (int rowIndex = ignoreRows; rowIndex 《= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize 》 rowSize) {
rowSize = tempRowSize;
}
String values = new String;
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = 0; columnIndex 《= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// 注意：一定要设成这个，否则可能会出现乱码
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
values = rightTrim(value);
hasValue = true;
}
if (hasValue) {
result.add(values);
}
}
}
in.close();
String returnArray = new String;
for (int i = 0; i 《 returnArray.length; i++) {
returnArray) result.get(i);
}
return returnArray;
}
/**
* 去掉字符串右边的空格
* @param str 要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i 》= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
}
如何用Excel处理200万行以上数据
关于如何用Excel处理200万行以上数据问题，现在已经完全没有压力了，虽然Excel工作表本身只支持1048576行数据，而且如果真的在一个表里数据导到100万行以上，这个表基本就跑不动了。
但是，随着Excel2016的内置新功能Power Query、Power Pivot等（Excel2010或Excel2013可到微软官方下载相应的插件）的推出，这个问题已经得到很好的解决。
理论上，Power Query和Power Pivot支持的数据行数是没有限制的，但Power Pivot仅支持2G以下的数据文件。
大神高飞曾就Power Query和Power Pivot对大数据支持做过相关测试，情况如下：
Excel 一亿行数据分析实践（总结篇）
测试目的
本次测试目的并非与其他数据分析方法对比优劣、而是尝试介绍一种完全基于EXCEL的本地化大数据集处理方式。
分析人员常用的大数据处理方式
本次演示的方式
这种方式的优点
降低成本。减少工具间的切换成本，直接使用Excel作为存储和分析工具。
展现灵活。展现端继续使用Excel，发挥它灵活、自定义程度高的优势。
便于交付。其他方式得到的结果为了便于交付，还要导出为Excel，而现在整个分析流都在Excel内部完成。
结果可交互。PowerPivot相当于一个存储了源数据的OLAP引擎，通过控制切片器等外部筛选条件，可以迅速、动态的查看结果，使用其他方法，可能需要返回分析端改变计算条件重新导出。
测试项目一：数据导入和耗时
向Excel导入大数据，有两种方式：
PowerPivot导入，直接导入，不支持数据转换和清洗操作。
PowerQuery导入，在导入前可以对数据做预处理。
本次使用的测试数据集共有19列，有多列需要进行格式转换和日期提取操作，使用第一种方式，需要导入后在PowerPivot内部进行，使用方式二可以在载入前完成，很明显的是，对于方式二，预处理步骤越多，加载时间会越长。
下图展示了不同量级不同导入方式的耗时情况（单位：秒）
为了直接对比PowerQuery和PowerPivot的加载效率，增加了一个*号方式，这种方式不对数据做任何清洗转换，直接加载到模型，与PowerPivot步骤相同。
现象
对比前两行结果，PowerQuery的数据导入效率与PowerPivot不分伯仲。
PowerQuery没有数据量的限制，而PowerPivot不到导入超过2G的文件。
清洗步骤和数据量的增多，都会显著增加PowerQuery的导入时间，比如一亿行数据，即使三个简单的清洗步骤，用时已经超过了30分钟
结论
PowerPivot导入方式使用的是Access连接器，受限于Access文件本身的限制，不能导入超过2G的数据，这也说明，PowerPivot数据存储能力超过了Access。
PowerQuery是轻型ETL工具，处理大数据集性能不强（基于Excel版本的 PQ）。
如果尝试使用Buffer函数缓存数据，会发现这个缓存过程非常漫长，实际上，Buffer函数并不适合缓存大数据集，因为无法压缩数据，内存可能会很快爆掉。
测试项目二：文件压缩比率
对比不同导入方式生成的文件大小，与数据源文件做比较。
影响文件压缩比率的因素，主要是数据集本身的特征和PowerPivot引擎的性能
结论：
数量级越大，压缩比率越高。
同一数据量级，清洗步骤越多，最终文件会越大，并且随着数据量的增加，这种现象会越明显。
测试项目三：简单分析的效率
我们真正关心的内容是，Excel能否快速、高效的对大数据集开展分析。
简单分析定义的场景是，逐月统计有多少位顾客发生了购买。做法是把年和月拖入透视表行字段，将CustomerKey拖入值区域，修改值汇总方式为统计不重复值。
测试发现，即便使用一亿行数据，这个计算过程的用时也很短，小于1s。于是我增加了一点难度，加入两个切片器对结果做交叉筛选，计算用时仍然小于1s，看来PowerPivot处理这类分析比较轻松，最终此项测试没有计时。
测试项目四：复杂分析的效率
新客统计：逐月计算当月产生购买的顾客中，有多少是新客户（第一笔购买发生在当月）
为了获取到PowerPivot引擎的计算时间，测试在DAX Studio内完成，同时为了模拟透视表的计算结果，需要对公式做一点改动。
计算用时（毫秒）
二次运算的用时指的是首次运算结束后，不清空缓存再次执行重复计算所花费的时间。相比第一次运算，节约时间在30%左右。原因是DAX的两个引擎中，有一个可以缓存计算结果，被缓存的内容可以在之后被公式内部调用，也可以跨公式调用。
结合这个知识，对DAX的表达式进行优化，可以获得更好的性能表现，下面是新客统计优化之后的写法，我们来对比计值时间的变化。
优化后计算用时（毫秒）
可以看出引擎的缓存起到了显著效果，二次计算直接调用首次运算的结果，计算时间不随数据量的增加而增加。
以一亿行数据集的结果为例，对比算法优化前后的用时：
复杂统计测试项目二，流失客户统计
与新客的呈现方式相同，依然是逐月计算当月的流失客户，不同的是流失客户的定义更为复杂。
自定义一个流失天数，被判定流失的客户需同时满足以下两个条件：
所有在当月之前最后一次购买的日期+自定义流失天数，落在当前时间区间内。
当月如果发生购买，第一次购买日期不能早于判定流失的日期。
流失客户公式和计算结果
计值流如此复杂的一个公式，PowerPivot会耗时多久呢，我只用了一亿行数据的文件做测试，结果是首次计算4093ms，二次计算1720ms。
说明：
1. 以上测试模拟了透视表的呈现布局，而且你可以加入切片器改变公式的上下文条件，迅速得出特定产品、特定商户和特定促销活动的新客户以及流失客户，非常方便。
2. 时间统计基于少量的测试结果，存在一定偶然性，仅供参考。
测试环境
电脑配置也是影响计算性能的重要因素，需要说明的是，以上进行的所有测试都基于台式机，在做现场分享的时候，我在笔记本电脑上重新运行了一遍流失客户公式，两个环境的用时如下：
结合平时其他测试，我的笔记本执行同样的计算，用时平均在台式机的两倍左右。两台电脑的配置如下
注意：提升CPU主频、核心数、1、2、3级缓存；内存的大小和频率都会提升引擎的性能表现。
总结
对于本地化大数据集的分析，本文提供了一种新的可能，严格来讲，2010年的时候你已经可以使用，只不过彼时它羽翼未丰，计算性能和稳定性难堪大任。
而现在，你已经见识到了这套工具（PowerPivot+PowerQuery）的能力，无论大数据还是复杂运算，Excel公式和VBA已经无法望其项背。
一般说来，积累通常是好事，财富可以通过积累不断增加、写作能力可以通过积累不断增强，但在某些知识领域，迭代是如此的快速和彻底，以至于底层的方法论都将被淘汰掉，过去的知识成为此刻的负担，你最好尽快丢掉过去，拥抱未来
从上面可以看出，使用Excel处理200万行的数据完全没有问题，那么，Power Query和Power Pivot怎么用呢？可以参考以下系列内容：
