【狼窝乀野狼】Excel那些事儿
在工作中我们常常遇到Excel表格，不管是数据的导入导出，还是财务统计什么都，都离不开Excel，Excel是我见过的最牛逼的一个软件（可能我的见识少）没有之一；如果你只停留在Excel处理数据，统计数据的层面，那么我想说，你比我的见识还少（你要是不服，不服就算了）。Excel不仅仅是统计数据，处理数据还可以画画，制作flash动画etc。
好了，言归正传，我是一名程序员，入行也就几年，见识和高度也都还停留在表现层，非要问我出处，我只能说四川某高校。其实我想写这个博客已经很久了，公司里面内部博客已经写完，但是遇到一个不能访问外网的公司我也醉了（信息安全）。大体要讲的就是我们平时工作中常用的几种方式来处理Excel（其实也就是我工作中用到的几种方式）。
1、传统的采用Office.Interop
其实要说Office.Interop这个不管从那个方面，应该算比较好也比较直接操作Excel，各种样式什么的肯定是必须支持，毕竟是自家产品不能被打脸，不是么？但是，我这里说说不好的情况。首先你本地必须安装MS Office，为啥呢？我们用C#调用的时候，是调用Office自己的组件，需要依赖Office如若不安装那肯定是玩不转的。另外，这是我在工作中踩过坑的，那就是你还必须激活，没错是激活。没有激活会爆出一些底层提示，查询了各种资料发现，这鬼居然要激活采能够用，orz!!!
原本这里应该有Code的，不是我懒，而是如果采用Office.Interop组件操作Excel网上比比皆是，如果需要可以私信给我，我可以加上。
2、采用NPOI
话说NPOI，这是一个开源组件，是仿POI，如果有Java的同学应该知道，Java里面操作Excel就是用的POI（可能这里会出现调侃点），NPOI操作Excel和Office.Interop操作Excel有什么地方不一样呢？首先，也是主要的，你不需要安装Office。想想，我做一个网站，其实就是想将数据导出Excel给用户，我服务器上面难道还要安装一个Office，就是方便为了处理数据给用户？这其实没有必要嘛，再说虽然网上有N多破解版什么激活码什么的，单单Office就有800+M，毕竟空间还是比较贵嘛，所以基于综上我觉得NPOI就可以了。NPOI操作Excel读取写入数据什么的都是没有问题，样式什么的也肯定支持。以前的NPOI只支持Office2003，现在可以支持更高版本了。
http://www.cnblogs.com/luxiaoxun/p/3374992.html 这是园子中另一个同学关于NPOI和Aspose.Cell的一个介绍。
3、采用Aspose.Cell
[Aspose.Cells是一款功能强大的Excel文档处理和转换控件，开发人员和客户电脑无需安装Microsoft Excel也能在应用程序中实现类似Excel的强大数据管理功能，支持所有Excel格式类型的操作，在没有Microsoft Excel的环境下，用户也可为其应用程序嵌入类似Excel的强大数据管理功能。Aspose.Cells可以对每一个具体的数据，表格和格式进行管理，在各个层面导入图像，应用复杂的计算公式，并将应用程序中的表格保存为各种格式等]【源于百度百科】，从介绍上面看，好像Office Excel能处理的，她都能处理嘛，不过可惜的是她不是开源的这鬼要Money，道听途说的，没有Licence的Asponse.Cell导出的Excel有水印，这里我没有去证实，当然网上有非官方手段的dll。
public static void OutFileToDisk(DataTable dt,string tableName,string path)
{
Workbook workbook = new Workbook();
//工作簿
Worksheet sheet = workbook.Worksheets[0];
//工作表
Cells cells = sheet.Cells;
//单元格
//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()];
//新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
styleTitle.Font.Name = "宋体";
//文字字体
styleTitle.Font.Size = 18;
//文字大小
styleTitle.Font.IsBold = true;
//粗体
//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()];
//新增样式
style2.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
style2.Font.Name = "宋体";
//文字字体
style2.Font.Size = 14;
//文字大小
style2.Font.IsBold = true;
//粗体
style2.IsTextWrapped = true;
//单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//样式3
Style style3 = workbook.Styles[workbook.Styles.Add()];
//新增样式
style3.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
style3.Font.Name = "宋体";
//文字字体
style3.Font.Size = 12;
//文字大小
style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
int Colnum = dt.Columns.Count;
//表格列数
int Rownum=dt.Rows.Count;
//表格行数
//生成行1 标题行
cells.Merge(0, 0, 1, Colnum);
//合并单元格
cells[0, 0].PutValue(tableName);
//填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);
//生成行2 列名行
for (int i = 0; i < Colnum; i++)
{
cells[1, i].PutValue(dt.Columns[i].ColumnName);
cells[1, i].SetStyle(style2); cells.SetRowHeight(1, 25);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[2 + i, k].PutValue(dt.Rows[i][k].ToString());
cells[2 + i, k].SetStyle(style3);
}
cells.SetRowHeight(2+i, 24);
} workbook.Save(path);
}
public MemoryStream OutFileToStream(DataTable dt, string tableName)
{
Workbook workbook = new Workbook();
//工作簿
Worksheet sheet = workbook.Worksheets[0];
//工作表
Cells cells = sheet.Cells;
//单元格
//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()];
//新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
styleTitle.Font.Name = "宋体";
//文字字体
styleTitle.Font.Size = 18;
//文字大小
styleTitle.Font.IsBold = true;
//粗体
//样式2
Style style2 = workbook.Styles[workbook.Styles.Add()];
//新增样式
style2.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
style2.Font.Name = "宋体";
//文字字体
style2.Font.Size = 14;
//文字大小
style2.Font.IsBold = true;
//粗体
style2.IsTextWrapped = true;
//单元格内容自动换行
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//样式3
Style style3 = workbook.Styles[workbook.Styles.Add()];
//新增样式
style3.HorizontalAlignment = TextAlignmentType.Center;
//文字居中
style3.Font.Name = "宋体";
//文字字体
style3.Font.Size = 12;
//文字大小
style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
int Colnum = dt.Columns.Count;
//表格列数
int Rownum = dt.Rows.Count;
//表格行数
//生成行1 标题行
cells.Merge(0, 0, 1, Colnum);
//合并单元格
cells[0, 0].PutValue(tableName);
//填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);
//生成行2 列名行
for (int i = 0; i < Colnum; i++)
{
cells[1, i].PutValue(dt.Columns[i].ColumnName);
cells[1, i].SetStyle(style2);
cells.SetRowHeight(1, 25);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[2 + i, k].PutValue(dt.Rows[i][k].ToString());
cells[2 + i, k].SetStyle(style3);
}
cells.SetRowHeight(2 + i, 24);
}
MemoryStream ms = workbook.SaveToStream(); return ms;
}
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.License li = new Aspose.Cells.License();
string lic = "";
Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic));
li.SetLicense(s);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 10;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0; for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
path = Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return succeed;
}
4、采用OleDb
OLEDB是采用数据库连接方式进行读取数据，也就是采用我们SQL语句进行读写操作，她读取的数据或者插入的数据是没有格式的（Office2003除外，在Office2003如果模版表格有格式，那么插入的数据格式会于表头保持一致）。采用数据库方式进行连接读取那么肯定需要数据库驱动，和其他数据库一样，需要一个Access的一个数据驱动器，没有这个也是肯定玩不转的，就像你要用Aspose.Cells没有这个dll肯定是用不了的。采用数据库读取，方便快捷。
Office2003采用连接方式为：Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source =filePath;Extended Properties=Excel 8.0
Office2007+版本连接方式：Provider=Microsoft.Ace.OleDb.12.0;data source=filePath;Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'
public class OledbProcessExcel
{
private const string XLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0";
private const string XLSX = "Provider=Microsoft.Ace.OleDb.12.0;data source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
private string m_Connstr;
public OledbProcessExcel(string filePath)
{
FileInfo info = new FileInfo(filePath);
if(info.Extension.ToLower() == ".xls")
{
m_Connstr = string.Format(XLS,filePath);
}
else
{
m_Connstr = string.Format(XLSX,filePath);
}
}
public int ExecuteNonQuery(string cmdText, IDbDataParameter[] param)
{
using (OleDbConnection conn = new OleDbConnection(m_Connstr))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(param);
return cmd.ExecuteNonQuery();
}
}
}
public object ExecuteScalar(string cmdText, IDbDataParameter[] param)
{
using (OleDbConnection conn = new OleDbConnection(m_Connstr))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(param);
return cmd.ExecuteScalar();
}
}
}
public DataTable ExecuteForDataTable(string cmdText, IDbDataParameter[] param)
{
using (OleDbConnection conn = new OleDbConnection(m_Connstr))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(param);
DataTable dt = new DataTable();
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
adapter.Fill(dt);
return dt;
}
}
}
}
}
5、Excel2CSV
其实这种方式主要在于对一次性需要将数据读取出来，做的一个保护，类似我司中就Excel大于5M的Excel就会转为CSV采用IO读取的方式进行，我们都知道采用数据库方式连接吐出DataTable方式操作是方便的，但是DataTable是一个复杂的数据结构，在数据量较大的情况容易内存溢出，我司的数据基本都是W为单位的，所以做了一个简单的转换处理，而转换也是采用的另一个程序进行转换的，主要还是防止内存溢出。
6、总结
博客在于生活工作中的点点滴滴的记录，不是每个人的剧本都一样。同样，上述的功能或者实现不是每个人都适用，我这里仅仅是一个抛砖引玉的一个作用，如果某位同学发现内容有误，或者侵犯版权，请及时联系我，核实后我及时更正。如有转载麻烦请写明出处，最后采用一句经典话语收尾“生活是如此的精彩，生命是如此的辉煌”。
