/// <summary>
/// 功能:导出Excel表格
/// 作者:付强
/// 日期:2013年7月9日
/// </summary>
/// <param name="fileName">要保存的文件路径及名称</param>
/// <param name="reportSource">数据源</param>
private void ExportExcel(string fileName, TableReport reportSource)
{
Microsoft.Office.Interop.Excel._Application xlsApp = new ApplicationClass();
if (xlsApp == null)
{
return;
}
Workbook xlsBook = xlsApp.Workbooks.Add(true);
Worksheet xlSheet = (Worksheet)xlsBook.Worksheets[1];
xlSheet.Cells[1, 1] = reportSource.ReportName;
Range range = xlSheet.get_Range(xlsApp.Cells[1, 1], xlsApp.Cells[1, reportSource.ReportFields.Length]);
range.MergeCells = true;
range.Font.Size = 24;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (!string.IsNullOrEmpty(reportSource.XMMC))
{
xlSheet.Cells[2, 1] = "项目名称:" + reportSource.XMMC;
range = xlSheet.get_Range(xlsApp.Cells[2, 1], xlsApp.Cells[2, 2]);
range.MergeCells = true;
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
if (!string.IsNullOrEmpty(reportSource.ZYMC))
{
xlSheet.Cells[2, 3] = "专业名称:" + reportSource.ZYMC;
range = xlSheet.get_Range(xlsApp.Cells[2, 3], xlsApp.Cells[2, reportSource.ReportFields.Length]);
range.MergeCells = true;
range.HorizontalAlignment = XlHAlign.xlHAlignRight;
}
System.Data.DataTable dt = reportSource.DataSource as System.Data.DataTable;
int rowSpan = 3, colSpan = 0, position = 0;
string band = "";
for (int i = 0; i < reportSource.ReportFields.Length; i++)
{
FieldReport item = reportSource.ReportFields[i];
if (item.Bands.Equals(string.Empty))
{
//xlSheet.Cells[rowSpan + 1, i + 1] = item.Caption;
range = xlSheet.get_Range(xlsApp.Cells[rowSpan, i + 1], xlsApp.Cells[rowSpan + 1, i + 1]);
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.MergeCells = true;
xlSheet.Cells[rowSpan, i + 1] = item.Caption;
range.Font.Bold = true;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.ColumnWidth = item.RowWidth / 15;
position += 1;
colSpan = 0;
}
else
{
if (!band.Equals(item.Bands))
{
position += colSpan;
colSpan = 0;
}
//xlSheet.Cells[rowSpan, i + 1] = item.Bands;
range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[rowSpan, i + 1];
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range = xlSheet.get_Range(xlsApp.Cells[rowSpan, position + 1], xlsApp.Cells[rowSpan, position + 1 + colSpan]);
range.MergeCells = true;
xlSheet.Cells[rowSpan, position + 1] = item.Bands;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.EntireColumn.AutoFit();
xlSheet.Cells[rowSpan + 1, i + 1] = item.Caption;
range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[rowSpan + 1, i + 1];
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
range.ColumnWidth = item.RowWidth / 15;
colSpan++;
band = item.Bands;
}
}
for (int r = 0; r < dt.Rows.Count; r++)
{
if (reportSource.ReportFields.Length >= dt.Columns.Count)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName.Equals("XMBM"))
{
xlSheet.Cells[r + 5, i + 1] = "'" + dt.Rows[r][i].ToString();
}
else
{
xlSheet.Cells[r + 5, i + 1] = dt.Rows[r][i].ToString();
}
range = xlSheet.get_Range(xlsApp.Cells[r + 5, i + 1], xlsApp.Cells[r + 5, i + 1]);
range.Borders.LineStyle = XlLineStyle.xlContinuous;
}
}
else
{
for (int i = 0; i < dt.Columns.Count - (dt.Columns.Count - reportSource.ReportFields.Length); i++)
{
xlSheet.Cells[r + 5, i + 1] = dt.Rows[r][i].ToString();
range = xlSheet.get_Range(xlsApp.Cells[r + 5, i + 1], xlsApp.Cells[r + 5, i + 1]);
range.Borders.LineStyle = XlLineStyle.xlContinuous;
}
}
}
xlsBook.Saved = true;
xlsBook.SaveCopyAs(fileName);
}
/// <summary>
/// 创建表格头
/// </summary>
/// <param name="rpf">报表个字段格式</param>
/// <param name="border"></param>
/// <returns></returns>
private PageHeaderBand CreateTableHeader(TableReport p_info)
{
PageHeaderBand new_PageHeaderBand = new PageHeaderBand();
new_PageHeaderBand.Controls.Add(CreateTitleHeader(p_info));
if (p_info.ReportFields != null)
{
XRTable headerTable = null;
XRTableRow headerOneRow = null; //一级表头
XRTableRow headerTwoRow = null; //二级表头
string bands = "**********"; //上一和并列的值
for (int i = 0; i < p_info.ReportFields.Length; i++)
{
FieldReport item = p_info.ReportFields[i];
if (item.Bands == string.Empty)
{
if (item.Bands != bands)
{
bands = item.Bands;
headerTable = new XRTable();
headerTable.Dpi = 254F;
headerTable.Width = 0;
headerTable.Height = 112;
headerOneRow = new XRTableRow();
headerOneRow.Dpi = 254F;
headerOneRow.Height = 112;
}
XRTableCell headerCell = new XRTableCell();
headerCell.Dpi = 254F;
headerCell.Font = item.HeaderFont;
headerCell.Borders = (BorderSide)(BorderSide.Top | BorderSide.Right | BorderSide.Left | BorderSide.Bottom);
headerCell.BorderWidth = 1;
int width = 0;
if (new_PageHeaderBand.Controls.Count > 1 && headerTable.Width == 5)
{
width = 3;
}
headerCell.Width = item.RowWidth + width;
headerCell.WordWrap = true;
headerCell.CanGrow = true;
headerCell.TextAlignment = this.Alignments(item.TopAlignment);
headerCell.Text = item.Caption;
headerCell.Tag = item.Field;
headerTable.Width += item.RowWidth - (headerTable.Width == 5 ? 5 : 0);
headerOneRow.Cells.Add(headerCell);
if (item.Bands != p_info.ReportFields[i + (i == p_info.ReportFields.Length - 1 ? 0 : 1)].Bands || i == p_info.ReportFields.Length - 1)
{
headerTable.Rows.Add(headerOneRow);
headerTable.Location = GetTable(new_PageHeaderBand);
if (new_PageHeaderBand.Controls.Count > 1)
{
headerTable.Width = headerTable.Width + 3;
}
new_PageHeaderBand.Controls.Add(headerTable);
new_PageHeaderBand.Height = 0;
}
}
else
{
if (item.Bands != bands)
{
bands = item.Bands;
headerTable = new XRTable();
headerTable.Dpi = 254F;
headerTable.Width = 0;
headerTable.Height = 112;
headerOneRow = new XRTableRow();
headerOneRow.Dpi = 254F;
headerOneRow.Height = 56;
XRTableCell headerOneCell = new XRTableCell();
headerOneCell.Dpi = 254F;
headerOneCell.Font = item.HeaderFont;
headerOneCell.Borders = (BorderSide)(BorderSide.Top | BorderSide.Right | BorderSide.Left);
headerOneCell.BorderWidth = 1;
headerOneCell.Width = item.RowWidth;
headerOneCell.WordWrap = true;
headerOneCell.CanGrow = true;
headerOneCell.TextAlignment = this.Alignments(item.TopAlignment);
headerOneCell.Text = item.Bands;
headerOneCell.Tag = item.Field;
headerOneRow.Cells.Add(headerOneCell);
headerTable.Rows.Add(headerOneRow);
headerTwoRow = new XRTableRow();
headerTwoRow.Dpi = 254F;
headerTwoRow.Height = 56;
}
XRTableCell headerTwoCell = new XRTableCell();
headerTwoCell.Dpi = 254F;
headerTwoCell.Font = item.HeaderFont;
headerTwoCell.Borders = (BorderSide)(BorderSide.Top | BorderSide.Right | BorderSide.Left | BorderSide.Bottom);
headerTwoCell.BorderWidth = 1;
int width = 0;
if (new_PageHeaderBand.Controls.Count > 1 && headerTable.Width == 5)
{
width = 3;
}
headerTwoCell.Width = item.RowWidth + width;
headerTwoCell.WordWrap = true;
headerTwoCell.CanGrow = true;
headerTwoCell.TextAlignment = this.Alignments(item.TopAlignment);
headerTwoCell.Text = item.Caption;
headerTable.Width += item.RowWidth - (headerTable.Width == 5 ? 5 : 0);
headerTwoRow.Cells.Add(headerTwoCell);
if (item.Bands != p_info.ReportFields[i + (i == p_info.ReportFields.Length - 1 ? 0 : 1)].Bands || i == p_info.ReportFields.Length - 1)
{
headerTable.Rows.Add(headerTwoRow);
headerTable.Location = GetTable(new_PageHeaderBand);
if (new_PageHeaderBand.Controls.Count > 1)
{
headerTable.Width = headerTable.Width + 3;
}
new_PageHeaderBand.Controls.Add(headerTable);
new_PageHeaderBand.Height = 0;
}
}
}
}
return(new_PageHeaderBand);
}