/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">DataTable对象</param>
private static void ToExcel(string startTime, string endTime, string fileName, DataTable dt)
{
ReportExportToExcel xlExport = new ReportExportToExcel();
string filePath = "E:\\Publish\\UploadFile\\wlkg\\report\\";
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
string saveFilePath = string.Format("{0}{1}.xls", filePath, fileName);
try
{
xlExport.CreatExcelSheets();
xlExport.SetCurActiveSheet();
//输出保税部经营情况表头到Excel
_WriteHeader(startTime, endTime, xlExport);
//输出保税部经营情况表数据到Excel
_WriteBody(dt, xlExport);
//保存
xlExport.save(saveFilePath);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//释放
xlExport.Dispose();
}
}
/// <summary>
/// 输出表头
/// </summary>
/// <param name="startTime">起始时间</param>
/// <param name="endTime">终止时间</param>
/// <param name="xlExport">xlExport对象</param>
private static void _WriteHeader(string startTime, string endTime, ReportExportToExcel xlExport)
{
//插入文字
_ExcelCellValue[] excelCellValue = new _ExcelCellValue[]
{
new _ExcelCellValue(1, 3, "保税部" + startTime + "至" + endTime + "经营情况统计"),
new _ExcelCellValue(2, 1, "项目"),
new _ExcelCellValue(2, 2, "船名"),
new _ExcelCellValue(2, 3, "货主"),
new _ExcelCellValue(2, 4, "货名"),
new _ExcelCellValue(2, 5, "数量（吨）/TEU"),
};
//插入文字
xlExport.SetCellSetValue(excelCellValue, 1, 1, 2, 5);
//设置列宽(第一列到最后一列)
xlExport.SetColumnWidth(1, 5, 20);
//设置行高
xlExport.SetRowHeight(1, 2, 14);
//设置（行1，列1，行2，列5）居中
Range range = xlExport.GetRange(1, 1, 2, 5);
xlExport.SetCellsAlignment(range);
//设置表头边框
xlExport.SetAllCellsBorderExclusiveRL(2, 1, 2, 5, ExcelBorderWeight.Thin);
}
/// <summary>
/// 输出表头
/// </summary>
/// <param name="startTime">起始时间</param>
/// <param name="endTime">终止时间</param>
/// <param name="xlExport">xlExport对象</param>
private static void _WriteHeader(string startTime, string endTime, ReportExportToExcel xlExport)
{
//插入文字
_ExcelCellValue[] excelCellValue = new _ExcelCellValue[]
{
new _ExcelCellValue(1, 3, startTime + "至" + endTime + "客户经营报表"),
new _ExcelCellValue(2, 1, "客户名称"),
new _ExcelCellValue(2, 2, "报税类型"),
new _ExcelCellValue(2, 3, "货名"),
new _ExcelCellValue(2, 4, "船名"),
new _ExcelCellValue(2, 5, "提单数（吨）"),
new _ExcelCellValue(2, 6, "放货状态"),
new _ExcelCellValue(2, 7, "入库总数（吨）"),
new _ExcelCellValue(2, 8, "已出货（吨）"),
new _ExcelCellValue(2, 9, "余货（吨）"),
new _ExcelCellValue(2, 10, "已生成账单（元）"),
new _ExcelCellValue(2, 11, "已付款（元）"),
new _ExcelCellValue(2, 12, "未付款（吨）"),
};
//插入文字
xlExport.SetCellSetValue(excelCellValue, 1, 1, 2, 12);
//设置列宽(第一列到最后一列)
xlExport.SetColumnWidth(1, 12, 20);
//设置行高
xlExport.SetRowHeight(1, 2, 14);
//设置（行1，列1，行2，列12）居中
Range range = xlExport.GetRange(1, 1, 2, 12);
xlExport.SetCellsAlignment(range);
//设置表头边框
xlExport.SetAllCellsBorderExclusiveRL(2, 1, 2, 12, ExcelBorderWeight.Thin);
}
/// <summary>
/// 输出表数据
/// </summary>
/// <param name="dt">DataTable对称</param>
/// <param name="xlExport">ReportExportToExcel对象</param>
private static void _WriteBody(DataTable dt, ReportExportToExcel xlExport)
{
int headerRows = 2;
//插入数据集报表数据
xlExport.DataTableToExcel(dt, 1, 1, dt.Rows.Count, 5, headerRows + 1, 1);
//设置数报表数据单元格为文本形式
Range range = xlExport.GetRange(headerRows + 1, 1, dt.Rows.Count + headerRows + 1, 5);
xlExport.SetTextForm(range);
//设置行高
xlExport.SetRowHeight(headerRows + 1, headerRows + dt.Rows.Count + 1, 14);
//设置数据集报表数据对齐方式（行3，列1，行末，列5）
range = xlExport.GetRange(headerRows + 1, 1, dt.Rows.Count + headerRows, 5);
xlExport.SetCellsAlignment(range, ExcelHorizontalAlignment.Center);
//设置报表数据边框
xlExport.SetAllCellsBorderExclusiveRLTB(headerRows + 1, 1, dt.Rows.Count + headerRows, 5, ExcelBorderWeight.Thin);
}
/// <summary>
/// 输出表数据
/// </summary>
/// <param name="dt">DataTable对称</param>
/// <param name="xlExport">ReportExportToExcel对象</param>
private static void _WriteBody(DataTable dt, ReportExportToExcel xlExport)
{
//数据集报表数据
string[] colNameSet = new string[]
{ "B", "L", "D", "C", "E", "F", "I", "J",
"N", "G", "H", "M", };
int headerRows = 2;
//插入数据集报表数据
xlExport.DataTableToExcel(dt, colNameSet, headerRows + 1, 1);
//设置数报表数据单元格为文本形式
Range range = xlExport.GetRange(headerRows + 1, 1, dt.Rows.Count + headerRows + 1, 12);
xlExport.SetTextForm(range);
//设置行高
xlExport.SetRowHeight(headerRows + 1, headerRows + dt.Rows.Count + 1, 14);
//设置数据集报表数据对齐方式（行3，列1，行末，列12）
range = xlExport.GetRange(headerRows + 1, 1, dt.Rows.Count + headerRows, 12);
xlExport.SetCellsAlignment(range, ExcelHorizontalAlignment.Center);
//设置报表数据边框
xlExport.SetAllCellsBorderExclusiveRLTB(headerRows + 1, 1, dt.Rows.Count + headerRows, 12, ExcelBorderWeight.Thin);
}
