using System;
using Excel=Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Web;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace CCC.Utility
{
/// <summary>
/// Summary description for GenerateReportExcel.
/// </summary>
public class GenerateReportExcel
{
public GenerateReportExcel()
{
//
// TODO: Add constructor logic here
//
}
public static void DataTable2Excel(System.Data.DataTable dt)
{
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string strBulidExportHTML = BuildExportHTML(dt);
DirectoryInfo di = new DirectoryInfo(".");
string strLogFolder = di.FullName + "\\OutPut\\";
string strFileName = strLogFolder + fileName;
StreamWriter sw = new StreamWriter(strFileName, false, System.Text.Encoding.Default);
sw.Write(strBulidExportHTML);
sw.Flush();
sw.Close();
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string BuildExportHTML(System.Data.DataTable dt)
{
string result = string.Empty;
int readCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
int pagerecords = 50000;
result = "<?xml version=\"1.0\" encoding=\"gb2312\"?>";
result += "<?mso-application progid=\"Excel.Sheet\"?>";
result += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:o=\"urn:schemas-microsoft-com:office:office\" ";
result += "xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ";
result += "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ";
string strTitleRow = "";
strTitleRow = "<Row ss:AutoFitHeight='0'>";
for (int j = 0; j < colCount; j++)
{
strTitleRow += "<Cell><Data ss:Type=\"String\">" + dt.Columns[j].ColumnName + "</Data></Cell>";
}
strTitleRow += "</Row>";
StringBuilder strRows = new StringBuilder();
int page = 1;
int cnt = 1;
int sheetcolnum = 0;
for (int i = 0; i < readCnt; i++)
{
strRows.Append("<Row ss:AutoFitHeight=\"0\">");
for (int j = 0; j < colCount; j++)
{
if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
{
if (dt.Rows[i][j].ToString() != string.Empty)
{
strRows.Append("<Cell><Data ss:Type=\"String\">" + Convert.ToDateTime(dt.Rows[i][j].ToString()).ToString("yyyy年MM月dd日") + "</Data></Cell>");
}
else
strRows.Append("<Cell><Data ss:Type=\"String\"></Data></Cell>");
}
else
{
strRows.Append("<Cell><Data ss:Type=\"String\">" + dt.Rows[i][j].ToString().Trim() + "</Data></Cell>");
}
}
strRows.Append("</Row>");
cnt++;
if (cnt >= pagerecords + 1)
{
sheetcolnum = cnt + 1;
result += "<Worksheet ss:Name=\"Sheet" + page.ToString() + "\"><Table ss:ExpandedColumnCount=\"" + colCount.ToString() + "\" ss:ExpandedRowCount=\"" + sheetcolnum.ToString() + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"104\" ss:DefaultRowHeight=\"13.5\">" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet>";
strRows.Remove(0, strRows.Length);
cnt = 1;
page++;
}
}
sheetcolnum = cnt + 1;
result = result + "<Worksheet ss:Name='Sheet" + page.ToString() + "'><Table ss:ExpandedColumnCount='" + colCount.ToString() + "' ss:ExpandedRowCount='" + sheetcolnum.ToString() + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='104' ss:DefaultRowHeight='13.5'>" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet></Workbook>";
return result;
}
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable)
{
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
DirectoryInfo di = new DirectoryInfo(".");
string strLogFolder = di.FullName + "\\OutPut\\";
string strFileName = strLogFolder + fileName;
if (tmpDataTable == null)
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Application xlApp = new ApplicationClass();
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
//xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
xlBook.SaveCopyAs(strFileName);
}
}
}