收到消息，下星期又有导出 Excel 报表的代码要写。
心想，不就是 OleDb 先 CREATE 表， 然后 INSERT 么？
都是体力活啊......
结果拿到纸张的报表，我就悲剧了。
报表的结构，像下面这种结构，行/列都不确定的
因为
行是日期，外部用户指定
列是物品，可能有，可能没有，取决于外部用户的日期范围。
如果在C#里面，先把所有出现过的物品，作为列名
然后再自己分别组合计算，最后再去 OleDb 那里去 CREATE + INSERT 嘛
这个报表又有个那么大的标题。
假如显示的报表，是一个 Sheet ，报表的数据在另外一个 Sheet 里面。
我C#导出 Excel 的时候，把数据写到一个 [数据Sheet] 里面。
显示的 [报表Sheet] 里面，设置好引用那个 [数据Sheet] 的数据。
想一下，应该是可行的。
既然应该是可行的，那么我C#里面，干脆连计算也别计算了。
因为这样的效果，在 Excel 里面，使用数据透视表，点个三五下，就结束的工作。
我还跑C#里面计算来计算去的，还容易出错。
首先嘛，先去创建一个模版 Excel, 2个 Sheet
1个[数据Sheet]，1个[报表Sheet]
然后[数据Sheet]里面，造点测试数据，
[报表Sheet] 里面，折腾好格式 与 数据透视表
这个模版就暂时保存为 Template.xls
然后这个文件，加入 C# 的项目，设置为 “如果较新则复制”
C# 项目，是一个 WinForm 的项目，就2按钮
1个是把数据 通过 OleDB 写进 Excel
1个是更新 Excel 中的 数据透视表信息。
按钮1的代码
/// <summary>
/// 使用模版文件创建 Excel 文件.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUseTemplate_Click(object sender, EventArgs e)
{
// 如果文件存在，先删除.
if (File.Exists(EXCEL_FILE_NAME))
{
File.Delete(EXCEL_FILE_NAME);
}
// 模版复制为目标文件.
string filePath = HttpContext.Current.Request.MapPath("temp/Template.xls");
File.Copy(filePath, EXCEL_FILE_NAME);
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + EXCEL_FILE_NAME + ";Extended Properties=Excel 8.0;";
//excel2007的ConnectionString，其中Microsoft.Jet.OLEDB.12.0必须安装AccessDatabaseEngine.exe
//String sConnectionString = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + EXCEL_FILE_NAME + ";Extended Properties=Excel 12.0;";
OleDbConnection cn = new OleDbConnection(sConnectionString);
// 打开连接.
cn.Open();
string sqlCreate =
@"CREATE TABLE [销售数据] ([日期] Date, [商品] VarChar, [数量] Int)";
OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
// 创建 Sheet
cmd.ExecuteNonQuery();
OleDbCommand icmd = new OleDbCommand();
icmd.Connection = cn;
icmd.CommandText = "INSERT INTO [销售数据] ([日期], [商品], [数量]) VALUES (@SaleData, @SaleGoods, @SaleMoney)";
for (int i = ; i < ; i++)
{
for (int j = ; j < ; j++)
{
OleDbParameter[] paraArray = new OleDbParameter[];
paraArray[] = new OleDbParameter("@SaleData", OleDbType.Date);
paraArray[] = new OleDbParameter("@SaleGoods", OleDbType.VarChar);
paraArray[] = new OleDbParameter("@SaleMoney", OleDbType.Integer);
paraArray[].Value = DateTime.Today.AddDays(-i);
paraArray[].Value = "商品" + j;
paraArray[].Value = i + j;
icmd.Parameters.Clear();
icmd.Parameters.AddRange(paraArray);
icmd.ExecuteNonQuery();
}
}
cn.Close();
MessageBox.Show("处理完毕！！！");
}
按钮2的代码
/// <summary>
/// 更新数据透视表的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnBuildData_Click(object sender, EventArgs e)
{
ExcelService service = new ExcelService();
// 打开 Excel.
service.OpenExcel();
// 打开 Excel 文件.
//service.OpenExcelFile(AppDomain.CurrentDomain.BaseDirectory + EXCEL_FILE_NAME);
service.OpenExcelFile(EXCEL_FILE_NAME);
// 设置 数据透视表数据.
service.AddPivotTable("销售数据", "统计报表", "数据透视表1");
// 保存 Excel 文件.
service.SaveExcelFile();
// 关闭 Excel.
service.CloseExcel();
MessageBox.Show("处理完毕！！！");
}
其中 ExcelService 的代码如下 ：
using System;
using System.Data;
using System.Drawing;
using Excel = Microsoft.Office.Interop.Excel;
namespace ZXCCLib.Web.UILoader.Test
{
/// <summary>
/// Excel 服务.
/// </summary>
public class ExcelService
{
#region OPEN WORKBOOK VARIABLES
private static object vk_missing = System.Reflection.Missing.Value;
private static object vk_visible = true;
private static object vk_false = false;
private static object vk_true = true;
private object vk_update_links = ;
private object vk_read_only = vk_true;
private object vk_format = ;
private object vk_password = vk_missing;
private object vk_write_res_password = vk_missing;
private object vk_ignore_read_only_recommend = vk_true;
private object vk_origin = vk_missing;
private object vk_delimiter = vk_missing;
private object vk_editable = vk_false;
private object vk_notify = vk_false;
private object vk_converter = vk_missing;
private object vk_add_to_mru = vk_false;
private object vk_local = vk_false;
private object vk_corrupt_load = vk_false;
#endregion
/// <summary>
/// Excel App.
/// </summary>
private Excel.Application xlApp;
/// <summary>
/// Excel 工作簿.
/// </summary>
private Excel.Workbook xlBook = null;
/// <summary>
/// Excel 工作表.
/// </summary>
private Excel.Worksheet xlSheet = null;
/// <summary>
/// 打开 Excel.
/// </summary>
public void OpenExcel()
{
// 启动 Excel.
xlApp = new Excel.ApplicationClass();
// 可见/不可见.
xlApp.Visible = true;
xlApp.UserControl = true;
xlApp.DisplayAlerts = true;
} /// <summary>
/// 退出 Excel.
/// </summary>
public void CloseExcel()
{
xlApp.DisplayAlerts = false;
xlApp.Workbooks.Close();
xlApp.Quit();
}
/// <summary>
/// 打开 Excel 文件.
/// </summary>
/// <param name="excelFile"></param>
public void OpenExcelFile(String excelFile)
{
// 打开文件.
xlBook = xlApp.Workbooks.Open(excelFile,
vk_update_links, vk_missing, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
// 取得第一个 Sheet.
foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
{
xlSheet = displayWorksheet;
break;
}
}
/// <summary>
/// 保存 Excel 文件.
/// </summary>
public void SaveExcelFile()
{
xlBook.Save();
}
/// <summary>
/// 另存为 Excel 文件.
/// </summary>
public void SaveAsExcelFile(String asFileName)
{
xlBook.SaveCopyAs(asFileName);
} /// <summary>
/// 关闭 Excel 文件.
/// </summary>
public void CloseExcelFile()
{
xlBook.Close(false, false, false);
}
/// <summary>
/// 选择工作表.
/// </summary>
/// <param name="sheetName"></param>
public void SelectSheet(String sheetName)
{
// 选择工作表.
xlSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName);
}
/// <summary>
/// 取得单元格文本.
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public String GetStringValue(int row, int col)
{
return xlSheet.get_Range(xlSheet.Cells[row, col], xlSheet.Cells[row, col]).Text.ToString().Trim();
}
/// <summary>
/// 设置单元格文本./// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="value"></param>
public void SetValue(int row, int col, String value)
{
// 取得范围.
Excel.Range range = xlSheet.get_Range(xlSheet.Cells[row, col], xlSheet.Cells[row, col]);
// 原有的数值.
String oldValue = range.Text.ToString().Trim();
// 设置数值.
xlSheet.Cells[row, col] = value;
// 修改背景色
range.Interior.ColorIndex = ;
// 添加备注.
range.AddComment("修改前数值：" + oldValue);
}
/// <summary>
/// 设置指定单元格为 字符格式.
/// </summary>
/// <param name="row1"></param>
/// <param name="col1"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
public void SetTextFormat(int row1, int col1, int row2, int col2)
{
// 选择区域.
Excel.Range myrange = xlSheet.get_Range(xlSheet.Cells[row1, col1], xlSheet.Cells[row2, col2]);
// 文本格式
myrange.NumberFormatLocal = "@";
} /// <summary>
/// 更新数据透视表数据.
/// </summary>
/// <param name="dataSheerName">数据Sheet名</param>
/// <param name="pivotSheetName">报表Sheet名</param>
/// <param name="pivottableName">数据视图表名</param>
public void AddPivotTable(string dataSheerName, string pivotSheetName, string pivottableName)
{
// 首先定位到 数据的 Sheet. 设定 数据透视表的 的数据源.
xlSheet = null;
foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
{
if (dataSheerName == displayWorksheet.Name)
{
xlSheet = displayWorksheet;
break;
}
}
if (xlSheet != null)
{
// 取得数据的Sheet的行数与列数
int rowCount = xlSheet.UsedRange.Rows.Count;
int colCount = xlSheet.UsedRange.Columns.Count;
// 拼写好 数据源的名字，准备后面用于更新 数据透视表的数据源.
string sourceData = dataSheerName + "!R1C1:R" + rowCount + "C" + colCount;
// 然后定位到 数据透视表的 Sheet. 刷新数据.
xlSheet = null;
foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
{
if (pivotSheetName == displayWorksheet.Name)
{
xlSheet = displayWorksheet;
break;
}
}
if (xlSheet != null)
{
// 修改 Excel 文件中 数据透视表的 数据源
((Excel.PivotTable)xlSheet.PivotTables(pivottableName)).SourceData = sourceData;
// 刷新数据 : 重新计算 数据透视表数据
((Excel.PivotTable)xlSheet.PivotTables(pivottableName)).Update();
}
}
}
}
}
测试运行一下，按第一个按钮，先产生一个 [销售数据] 的Sheet.
按第2个按钮，
打开 Excel
设置 [统计报表]那个Sheet中，数据透视表的数据源为 [销售数据] 中的数据。
然后重新计算
最后保存
最后运行的结果如下：
---完---
