C#WinFormDataTable Excelの複数行ヘッダーのエクスポート、セルのマージ
今回のコードは従来のasp.NetDataTable 2 Excelクラスを修正し、最適化、使用方法とasp.Net版は同じです.
この例ではMicrosoftを用いた.Office.Interop.Excel.dll、バージョン12.0.4518.1014
asp.NetDataTable 2 Excel類の記事リンク:C#DataTable Excel進級複数行ヘッダー、連結セル、中国語ファイル名文字化け
この例ではMicrosoftを用いた.Office.Interop.Excel.dll、バージョン12.0.4518.1014
asp.NetDataTable 2 Excel類の記事リンク:C#DataTable Excel進級複数行ヘッダー、連結セル、中国語ファイル名文字化け
using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;
using System.IO;
using System.Text;
using System.Web.UI;
namespace DataTable2Excel
{
///
/// Common
/// :
/// :2016-4-8
///
public class Common
{
public Common()
{
//
//TODO:
//
}
///
/// : DataTable excel
/// :
/// :2016-4-8
///
///
///
///
/// : - ( 1 、2 、3 )
///
public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary mergeCellNums, int? mergeKey)
{
System.Web.UI.WebControls.GridView gvExport = null;
// IO excel
StringWriter strWriter = null;
HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// excel
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
// GridView
gvExport = new System.Web.UI.WebControls.GridView();
gvExport.DataSource = dtData.DefaultView;
gvExport.AllowPaging = false;
// , 、12-1
gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound);
gvExport.DataBind();
//
if (header != null && header.Length > 0)
{
gvExport.HeaderRow.Cells.Clear();
gvExport.HeaderRow.Cells.AddRange(header);
}
//
if (mergeCellNums != null && mergeCellNums.Count > 0)
{
foreach (int cellNum in mergeCellNums.Keys)
{
MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey);
}
}
//
gvExport.RenderControl(htmlWriter);
StreamWriter sw = new StreamWriter(fileName, false, Encoding.GetEncoding("gb2312"));
sw.Write("" + strWriter.ToString());
sw.Flush();
sw.Close();
sw.Dispose();
strWriter.Close();
strWriter.Dispose();
// Excel , Excel , , Excel2003 。
SaveAsExcel2003(fileName);
}
}
///
/// :
///
///
///
protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
foreach (TableCell cell in e.Row.Cells)
{
// , 、12-1
if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$"))
{
cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
}
}
///
/// : GridView
/// :
/// :2016-4-8
///
/// GridView
///
/// 1 、2 、3
///
public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey)
{
int i = 0, rowSpanNum = 1;
System.Drawing.Color alterColor = System.Drawing.Color.LightGray;
while (i < gvExport.Rows.Count - 1)
{
GridViewRow gvr = gvExport.Rows[i];
for (++i; i < gvExport.Rows.Count; i++)
{
GridViewRow gvrNext = gvExport.Rows[i];
if ((!mergeKey.HasValue || (mergeKey.HasValue && (gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text) || " ".Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode == 1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) || (mergeMode == 2 && " ".Equals(gvrNext.Cells[cellNum].Text.Trim())) || (mergeMode == 3 && (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text || " ".Equals(gvrNext.Cells[cellNum].Text.Trim())))))
{
gvrNext.Cells[cellNum].Visible = false;
rowSpanNum++;
//gvrNext.BackColor = gvr.BackColor;
if (alterColor == System.Drawing.Color.White)
gvrNext.BackColor = System.Drawing.Color.LightGray;
}
else
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
rowSpanNum = 1;
// ,
if (mergeKey.HasValue && cellNum == mergeKey.Value)
{
if (alterColor == System.Drawing.Color.White)
{
gvr.BackColor = System.Drawing.Color.LightGray;
alterColor = System.Drawing.Color.LightGray;
}
else
{
alterColor = System.Drawing.Color.White;
}
}
break;
}
if (i == gvExport.Rows.Count - 1)
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
if (mergeKey.HasValue && cellNum == mergeKey.Value)
{
if (alterColor == System.Drawing.Color.White)
gvr.BackColor = System.Drawing.Color.LightGray;
}
}
}
}
}
///
/// 2003 Excel
///
///
public static void SaveAsExcel2003(string filePath)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.DisplayAlerts = false; //
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Excel2003
wb.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
}
}