做一个基于Silverlight的应用,主要是报表数据生成展示,还需要将报表数据导出,在网上找了一些资料,发现不能用,于是进行了改进,并加入了读取所有分页数据的功能。
以下为基础代码:
/// <summary>
/// CSV字符串转换
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
private string FormatCSVField(string data)
{
return String.Format("{0}\t", data.Replace("\"", "\t\n"));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="dataPagerTemp">分页控件</param>
/// <param name="dataGridTemp">数据表格控件</param>
/// <returns></returns>
private string ExportExcel(DataPager dataPagerTemp, DataGrid dataGridTemp)
{
string colPath;
System.Reflection.PropertyInfo propInfo;
System.Windows.Data.Binding binding;
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
//加载Excel表头数据
List<string> headers = new List<string>();
for (int i = 0; i < dataGridTemp.Columns.Count; ++i)
{
string strTemp = dataGridTemp.Columns[i].Header.ToString();
headers.Add(FormatCSVField(strTemp));
}
strBuilder
.Append(String.Join("", headers.ToArray()))
.Append("\t\n");
//内容
for (int i = 0; i < dataPagerTemp.PageCount; i++)
{
dataPagerTemp.PageIndex = i;
foreach (Object m_Temp in dataGridTemp.ItemsSource)
{
List<string> csvRow = new List<string>();
foreach (DataGridColumn col in dataGridTemp.Columns)
{
if (col is DataGridBoundColumn)
{
binding = (col as DataGridBoundColumn).Binding;
colPath = binding.Path.Path;
string[] pathlist = colPath.Split('.');
object currentData = m_Temp;
int count = 0;
foreach (string item in pathlist)
{
propInfo = currentData.GetType().GetProperty(item);
if (propInfo == null) break;
count++;
if (count == pathlist.Length)
{
csvRow.Add(FormatCSVField(propInfo.GetValue(currentData, null).ToString()));
break;
}
else
{
currentData = propInfo.GetValue(currentData, null);
}
}
}
}
strBuilder
.Append(String.Join("", csvRow.ToArray()))
.Append("\t\n");
}
}
dataPagerTemp.PageIndex = 0;
return strBuilder.ToString();
}
/// <summary>
/// 保存为Excel
/// </summary>
/// <param name="dataPagerTemp">分页控件</param>
/// <param name="dataGridTemp">数据表格控件</param>
private void SaveAsExcel(DataPager dataPagerTemp, DataGrid dataGridTemp)
{
string data = ExportExcel(dataPagerTemp, dataGridTemp);
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "xls",
Filter = "Microsoft Office Excel(*.xls)|*.xls",
FilterIndex = 1
};
if (sfd.ShowDialog() == true)
{
using (Stream stream = sfd.OpenFile())
{
using (StreamWriter writer = new StreamWriter(stream, System.Text.UnicodeEncoding.Unicode))
{
data = data.Replace(",", "/t");
writer.Write(data);
writer.Close();
}
stream.Close();
}
}
}
调用:
/// <summary>
/// 导出报表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonExport_Click(object sender, RoutedEventArgs e)
{
SaveAsExcel(dataPagerReport, dataGridReport);
}