/// <summary>
/// 转换GridView为PDF文档
/// </summary>
/// <param name="sdr_Context">SqlDataReader</param>
/// <param name="title">标题名称</param>
/// <param name="fontpath_Title">标题字体路径</param>
/// <param name="fontsize_Title">标题字体大小</param>
/// <param name="fontStyle_Title">标题样式</param>
/// <param name="fontColor_Title">标题颜色</param>
/// <param name="fontpath_Col">列头字体路径</param>
/// <param name="fontsize_Col">列头字体大小</param>
/// <param name="fontStyle_Col">列头字体样式</param>
/// <param name="fontColor_Col">列头字体颜色</param>
/// <param name="col_Width">表格总宽度</param>
/// <param name="arr_Width">每列的宽度</param>
/// <param name="pdf_Filename">在服务器端保存PDF时的文件名</param>
/// <param name="FontPath">正文字体路径</param>
/// <param name="FontSize">正文字体大小</param>
/// <param name="fontStyle_Context">正文字体样式</param>
/// <param name="fontColor_Context">正文字体颜色</param>
/// <param name="para">Criteria rows / Report header, depending on the mode</param>
/// <returns>返回调用是否成功</returns>
public static void exp_Pdf(string[] ExtendedFields,
Dictionary <string, string> content, List <string> subtotal, List <string> subavg, List <string> SubCount, List <string> Count,
List <string> group, List <string> avg, List <string> sum, string companyname, string[] para, System.Data.DataTable sdr_Context, string title,
string fontpath_Title, float fontsize_Title, int fontStyle_Title, BaseColor fontColor_Title,
string fontpath_Col, float fontsize_Col, int fontStyle_Col, BaseColor fontColor_Col,
string tempFolderPath, string pdf_Filename,
string FontPath, float FontSize, int fontStyle_Context, BaseColor fontColor_Context,
string[] reportFooter = null)
{
bool showChangeOnly = ExtendedFields[0] == "1"; // CUSTOMERP.Model.REPORT.EXTENDFIELDs.ChangeOnly
bool hideCriteria = ExtendedFields[1] == "1"; //CUSTOMRP.Model.REPORT.EXTENDFIELDs.HideCriteria
Single tableWidth = (sdr_Context.Columns.Count * 200 >= 800 ? 800 : sdr_Context.Columns.Count * 200);
//在服务器端保存PDF时的文件名
string strFileName = pdf_Filename + ".pdf";
//初始化一个目标文档类
Document document = new Document(PageSize.A4.Rotate(), 0, 0, 10, 10);
//调用PDF的写入方法流
//注意FileMode-Create表示如果目标文件不存在，则创建，如果已存在，则覆盖。
//PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(HttpContext.Current.Server.MapPath(strFileName), FileMode.Create));
using (FileStream fs = new FileStream(tempFolderPath + strFileName, FileMode.Create))
{
PdfWriter writer = PdfWriter.GetInstance(document, fs);
//try
//{
//标题字体
BaseFont basefont_Title = BaseFont.CreateFont(fontpath_Title, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
Font font_Title = new Font(basefont_Title, fontsize_Title, fontStyle_Title, fontColor_Title);
Font font_COMPANY = new Font(basefont_Title, fontsize_Title - 3, fontStyle_Title, fontColor_Title);
Font font_CRITERIAL = new Font(basefont_Title, fontsize_Title - 4, fontStyle_Title, fontColor_Title);
Font font_DATETIME = new Font(basefont_Title, fontsize_Title - 4, fontStyle_Title, fontColor_Title);
//表格列字体
BaseFont basefont_Col = BaseFont.CreateFont(fontpath_Col, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
Font font_Col = new Font(basefont_Col, fontsize_Col, fontStyle_Col, fontColor_Col);
//正文字体
BaseFont basefont_Context = BaseFont.CreateFont(FontPath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
FontSelector selector = new FontSelector(); //Cheong - 2015/02/10 - use selector when phrase can have mixed language (like English and Chinese)
selector.AddFont(FontFactory.GetFont(FontFactory.COURIER, FontSize, fontStyle_Context, fontColor_Context));
selector.AddFont(new Font(basefont_Context, FontSize, fontStyle_Context, fontColor_Context)); // feeded is SimSun for CHS
selector.AddFont(FontFactory.GetFont("MHei-Medium", "UniCNS-UCS2-H", FontSize, fontStyle_Context, fontColor_Context)); // add CHT font
FontSelector boldselector = new FontSelector(); //Cheong - 2015/02/10 - use selector when phrase can have mixed language (like English and Chinese)
boldselector.AddFont(FontFactory.GetFont(FontFactory.COURIER, FontSize, fontStyle_Context | iTextSharp.text.Font.BOLD, fontColor_Context));
boldselector.AddFont(new Font(basefont_Context, FontSize, fontStyle_Context | iTextSharp.text.Font.BOLD, fontColor_Context)); // feeded is SimSun for CHS
boldselector.AddFont(FontFactory.GetFont("MHei-Medium", "UniCNS-UCS2-H", FontSize, fontStyle_Context | iTextSharp.text.Font.BOLD, fontColor_Context)); // add CHT font
FontSelector greyselector = new FontSelector(); //Cheong - 2015/02/10 - use selector when phrase can have mixed language (like English and Chinese)
greyselector.AddFont(FontFactory.GetFont(FontFactory.COURIER, FontSize, fontStyle_Context, BaseColor.LIGHT_GRAY));
greyselector.AddFont(new Font(basefont_Context, FontSize, fontStyle_Context, BaseColor.LIGHT_GRAY)); // feeded is SimSun for CHS
greyselector.AddFont(FontFactory.GetFont("MHei-Medium", "UniCNS-UCS2-H", FontSize, fontStyle_Context, BaseColor.LIGHT_GRAY)); // add CHT font
//打开目标文档对象
document.Open();
#region Company name
Paragraph Company = new Paragraph(companyname, font_COMPANY);
Company.IndentationLeft = 100;
Company.Alignment = Element.ALIGN_LEFT;
document.Add(Company);
#endregion Company name
#region Report title
Paragraph p_Title = new Paragraph(title, font_Title);
p_Title.Alignment = Element.ALIGN_LEFT;
p_Title.IndentationLeft = 100;
p_Title.SpacingAfter = 8;
document.Add(p_Title);
#endregion Report Title
#region Criteria
//v1.1.0 - Cheong - 2016/06/01 - Hide criteria text
if (!hideCriteria)
{
for (int i = 0; i < para.Length; i++)
{
Paragraph P_pa = new Paragraph(para[i], font_CRITERIAL);
P_pa.Alignment = Element.ALIGN_LEFT;
P_pa.IndentationLeft = 100;
document.Add(P_pa);
}
}
#endregion
#region Print on date
Paragraph P_date = new Paragraph("Print on : " + DateTime.Now.ToString("yyyy-MM-dd"), font_DATETIME);
P_date.Alignment = Element.ALIGN_LEFT;
P_date.IndentationLeft = 100;
document.Add(P_date);
P_date.SpacingBefore = 32;
#endregion
//根据数据表内容创建一个PDF格式的表
PdfPTable table = new PdfPTable(sdr_Context.Columns.Count);
table.SpacingBefore = 42;
table.TotalWidth = tableWidth; //表格总宽度
table.LockedWidth = true; //锁定宽度
List <int> arr = new List <int>();
for (int jj = 0; jj < sdr_Context.Columns.Count; jj++)
{
arr.Add(100);
}
table.SetWidths(arr.ToArray());//设置每列宽度
//构建列头
//设置列头背景色
table.DefaultCell.BackgroundColor = iTextSharp.text.BaseColor.LIGHT_GRAY;
//设置列头文字水平、垂直居中
table.DefaultCell.HorizontalAlignment = PdfPCell.ALIGN_CENTER;
table.DefaultCell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
// 告诉程序这行是表头，这样页数大于1时程序会自动为你加上表头。
table.HeaderRows = 1;
if (sdr_Context.Rows.Count > 0)
{
for (int i = 0; i < sdr_Context.Columns.Count; i++)
{
//v1.0.0 - Cheong - 2015/05/27 - Use Displayname instead
//table.AddCell(new Phrase(sdr_Context.Columns[i].ColumnName, font_Col));
table.AddCell(new Phrase(content[sdr_Context.Columns[i].ColumnName], font_Col));
}
// 添加数据
//设置标题靠左居中
table.DefaultCell.HorizontalAlignment = PdfPCell.ALIGN_LEFT;
// 设置表体背景色
table.DefaultCell.BackgroundColor = BaseColor.WHITE;
table.DefaultCell.BorderColor = BaseColor.WHITE;
//start set value to each cell.
int[] Subsum_Index = Function.IncWeb.getColumnIndexByColumnName(subtotal.ToArray(), sdr_Context.Columns);
int[] Subavg_Index = Function.IncWeb.getColumnIndexByColumnName(subavg.ToArray(), sdr_Context.Columns);
int[] Subcount_Index = Function.IncWeb.getColumnIndexByColumnName(SubCount.ToArray(), sdr_Context.Columns);
int[] sum_Index = Function.IncWeb.getColumnIndexByColumnName(sum.ToArray(), sdr_Context.Columns);
int[] avg_Index = Function.IncWeb.getColumnIndexByColumnName(avg.ToArray(), sdr_Context.Columns);
int[] count_Index = Function.IncWeb.getColumnIndexByColumnName(Count.ToArray(), sdr_Context.Columns);
int[] groupIndex = Function.IncWeb.getColumnIndexByColumnName(group.ToArray(), sdr_Context.Columns);
subTotal[] subTotalInfo = new subTotal[Subsum_Index.Length];
subAvg[] subAvginfo = new subAvg[Subavg_Index.Length];
subCount[] subcountinfo = new subCount[Subcount_Index.Length];
for (int x = 0; x < subTotalInfo.Length; x++)
{
subTotalInfo[x].ColumnIndex = Subsum_Index[x];
}
for (int y = 0; y < subAvginfo.Length; y++)
{
subAvginfo[y].ColumnIndex = Subavg_Index[y];
}
int tempRowsCount = 0;
for (int i = 0; i < sdr_Context.Rows.Count; i++)
{
//if (showType == "0")
//{
if (groupIndex.Length > 0)
{
//v1.0.0 - Cheong - 2016/03/18 - Insert new row before each group (except the first one)
if (i != 0)
{
for (int j = 0; j < sdr_Context.Columns.Count; j++)
{
table.AddCell(selector.Process(" "));
}
}
#region group header
bool GroupBegin = false;
if (i == 0)
{
GroupBegin = true;
}
else
{
for (int g_i = 0; g_i < groupIndex.Length; g_i++)
{
if (sdr_Context.Rows[i][groupIndex[g_i]].ToString() != sdr_Context.Rows[i - 1][groupIndex[g_i]].ToString())
{
GroupBegin = true;
break;
}
}
}
if (GroupBegin)
{
tempRowsCount = 0;
StringBuilder sb = new StringBuilder();
//v1.0.0 - Cheong - 2015/05/27 - Use DisplayName instead
//sb.AppendFormat("{0} : {1}", group[0], sdr_Context.Rows[i][groupIndex[0]]);
//sb.AppendFormat("{0} : {1}", content[group[0]], sdr_Context.Rows[i][groupIndex[0]]);
if (!Convert.IsDBNull(sdr_Context.Rows[i][groupIndex[0]]))
{
if (sdr_Context.Columns[groupIndex[0]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)sdr_Context.Rows[i][groupIndex[0]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sb.AppendFormat("{0} : {1}", content[group[0]], l_strDateTime);
//row.CreateCell(j).SetCellValue(((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));
}
else
{
sb.AppendFormat("{0} : {1}", content[group[0]], sdr_Context.Rows[i][groupIndex[0]]);
}
}
else
{
sb.AppendFormat("{0} : {1}", content[group[0]], String.Empty);
}
for (int g_i = 1; g_i < groupIndex.Length; g_i++)
{
//v1.0.0 - Cheong - 2015/05/27 - Same as above comment
//sb.AppendFormat(" {0} : {1}", group[g_i], sdr_Context.Rows[i][groupIndex[g_i]]);
//sb.AppendFormat(" {0} : {1}", content[group[g_i]], sdr_Context.Rows[i][groupIndex[g_i]]);
if (!Convert.IsDBNull(sdr_Context.Rows[i][groupIndex[g_i]]))
{
if (sdr_Context.Columns[groupIndex[g_i]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)sdr_Context.Rows[i][groupIndex[g_i]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sb.AppendFormat(" {0} : {1}", content[group[g_i]], l_strDateTime);
}
else
{
sb.AppendFormat(" {0} : {1}", content[group[g_i]], sdr_Context.Rows[i][groupIndex[g_i]]);
}
}
else
{
sb.AppendFormat(" {0} : {1}", content[group[g_i]], String.Empty);
}
}
if (sdr_Context.Columns.Count == 1)
{
table.AddCell(new PdfPCell(boldselector.Process(sb.ToString())) /*BorderWidthTop = table.DefaultCell.BorderWidthTop * 2*/ }
{
internal static string GetHtmlTableString(string[] ExtendedFields, Dictionary <string, string> content, List <string> subtotal, List <string> subavg, List <string> SubCount, List <string> Count, DataTable dt, List <string> sum, List <string> avg, List <string> group, string rptTitle = null, string comment = null,
string subcountLabel = "Sub Count", List <string> sortonCols = null, List <bool> isAscending = null, List <int> seq = null,
List <string> hideRows = null)
{
bool showChangeOnly = ExtendedFields[CUSTOMRP.Model.REPORT.EXTENDFIELDs.ReportType] == "1";
bool hideCriteria = ExtendedFields[CUSTOMRP.Model.REPORT.EXTENDFIELDs.HideCriteria] == "1";
StringBuilder sb = new StringBuilder();
int columnsCount = dt.Columns.Count;
if (rptTitle != null)
{
sb.AppendFormat("<div data-type=\"rptTitle\">{0}</div>", rptTitle);
}
if (comment != null)
{
sb.Append(comment);
}
//start set value to each cell.
int[] Subsum_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(subtotal.ToArray(), dt.Columns);
int[] Subavg_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(subavg.ToArray(), dt.Columns);
int[] Subcount_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(SubCount.ToArray(), dt.Columns);
int[] sum_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(sum.ToArray(), dt.Columns);
int[] avg_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(avg.ToArray(), dt.Columns);
int[] count_Index = WebHelper.getColumnIndexByColumnName_OrderbyIndex(Count.ToArray(), dt.Columns);
int[] groupIndex = WebHelper.getColumnIndexByColumnName(group.ToArray(), dt.Columns);
int[] sortIndex = WebHelper.getColumnIndexByColumnName(sortonCols.ToArray(), dt.Columns);
int[] hideIndex = WebHelper.getColumnIndexByColumnName(hideRows.ToArray(), dt.Columns);
Type[] numeric = new Type[3] {
typeof(Double), typeof(Decimal), typeof(Int32)
};
subTotal[] subTotalInfo = new subTotal[Subsum_Index.Length];
subAvg[] subAvginfo = new subAvg[Subavg_Index.Length];
subCount[] subcountinfo = new subCount[Subcount_Index.Length];
sb.Append("<table data-tblname=\"custom\">");
sb.AppendLine("<tr data-rowtype=\"header\">");
for (int m = 0; m < columnsCount; m++)
{
if (hideIndex.Contains(m))
{
continue;
}
DataColumn dc = dt.Columns[m];
//v1.0.0 - Cheong - 2016/03/21 - Align right for column headers of numeric fields
if ((dt.Columns[m].DataType == typeof(Double)) || (dt.Columns[m].DataType == typeof(Decimal)) || (dt.Columns[m].DataType == typeof(Int32)))
{
sb.Append("<th style=\"text-align: right\">" + content[dc.ColumnName] + "</th>");
}
else
{
//v1.0.0 - Cheong - 2015/05/27 - use DisplayName instead
//sb.Append("<th>" + dc.ColumnName + "</th>");
sb.Append("<th>" + content[dc.ColumnName] + "</th>");
}
}
sb.Append("</tr>");
for (int x = 0; x < subTotalInfo.Length; x++)
{
subTotalInfo[x].ColumnIndex = Subsum_Index[x];
}
for (int y = 0; y < subAvginfo.Length; y++)
{
subAvginfo[y].ColumnIndex = Subavg_Index[y];
}
OrderedEnumerableRowCollection <DataRow> tmp = null;
//bool grouped = false;
// 1. Sort columns which belong to a group (only if the user specified grouping).
if (sortIndex.Length > 0)
{
DateTime tpdatetime;
//grouped = true;
if (numeric.Contains(dt.Columns[sortIndex[0]].DataType))
{
tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => Convert.ToDouble(f[sortIndex[0]]))
: dt.AsEnumerable().OrderByDescending(f => Convert.ToDouble(f[sortIndex[0]]));
}
else if (dt.Columns[sortIndex[0]].DataType == typeof(DateTime))
{
tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => DateTime.TryParse(Convert.ToString(f[sortIndex[0]]), out tpdatetime) ? tpdatetime : DateTime.MinValue)
: dt.AsEnumerable().OrderByDescending(f => DateTime.TryParse(Convert.ToString(f[sortIndex[0]]), out tpdatetime) ? tpdatetime : DateTime.MinValue);
}
else
{
tmp = isAscending[0] ? dt.AsEnumerable().OrderBy(f => Convert.ToString(f[sortIndex[0]]))
: dt.AsEnumerable().OrderByDescending(f => Convert.ToString(f[sortIndex[0]]));
}
for (int z = 1; z < sortIndex.Count(); z++)
{
if (isAscending[z])
{
var thisI = z;
tmp = numeric.Contains(dt.Columns[sortIndex[thisI]].DataType) ? tmp.ThenBy(f => f[sortIndex[thisI]] is DBNull ? 0d : Convert.ToDouble(f[sortIndex[thisI]]))
: (dt.Columns[sortIndex[thisI]].DataType == typeof(DateTime) ? tmp.ThenBy(f => DateTime.TryParse(Convert.ToString(f[sortIndex[thisI]]), out tpdatetime) ? tpdatetime : DateTime.MinValue) : tmp.ThenBy(f => Convert.ToString(f[sortIndex[thisI]])));
}
else
{
var thisI = z;
tmp = numeric.Contains(dt.Columns[sortIndex[thisI]].DataType) ? tmp.ThenByDescending(f => f[sortIndex[thisI]] is DBNull ? 0d : Convert.ToDouble(f[sortIndex[thisI]]))
: (dt.Columns[sortIndex[thisI]].DataType == typeof(DateTime) ? tmp.ThenByDescending(f => DateTime.TryParse(Convert.ToString(f[sortIndex[thisI]]), out tpdatetime) ? tpdatetime : DateTime.MinValue) : tmp.ThenByDescending(f => Convert.ToString(f[sortIndex[thisI]])));
}
}
//v1.8.8 Ben 2019.10.31 - CopyToDataTable Must have rows otherwise it cause error
//dt = tmp.CopyToDataTable();
if (tmp.Any())
{
dt = tmp.CopyToDataTable();
}
}
int tempRowsCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!showChangeOnly)
{
if (groupIndex.Length > 0)
{
#region group header
bool GroupBegin = false;
if (i == 0)
{
GroupBegin = true;
}
else
{
for (int g_i = 0; g_i < groupIndex.Count(); g_i++)
{
if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i - 1][groupIndex[g_i]].ToString())
{
GroupBegin = true;
break;
}
}
}
if (GroupBegin)
{
tempRowsCount = 0;
if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]) && !string.IsNullOrEmpty(Convert.ToString(dt.Rows[i][groupIndex[0]])))
{
sb.Append("<tr data-rowtype=\"groupheader\">");
sb.AppendFormat("<td{0}>", dt.Columns.Count != 1 ? String.Format(" colspan=\"{0}\"", dt.Columns.Count) : String.Empty);
StringBuilder sbgrouptitle = new StringBuilder();
//v1.0.0 - Cheong - 2015/05/27 - Use Displayname instead
//sbgrouptitle.AppendFormat("{0} : {1}", group[0], dt.Rows[i][groupIndex[0]]);
//sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]);
if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]))
{
if (dt.Columns[groupIndex[0]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[0]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], l_strDateTime);
}
else
{
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]);
}
}
else
{
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], String.Empty);
}
for (int g_i = 1; g_i < groupIndex.Length; g_i++)
{
//v1.0.0 - Cheong - 2015/05/27 - Same as above comment
//sbgrouptitle.AppendFormat(" {0} : {1}", group[g_i], dt.Rows[i][groupIndex[g_i]]);
//sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]);
if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]))
{
if (dt.Columns[groupIndex[g_i]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[g_i]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], l_strDateTime);
}
else
{
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]);
}
}
else
{
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], String.Empty);
}
}
sb.Append(sbgrouptitle.ToString());
sb.Append("</td>");
sb.Append("</tr>");
}
}
#endregion
}
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
if (!Convert.IsDBNull(dt.Rows[i][j]))
{
if (dt.Columns[j].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sb.AppendFormat("<td>{0}</td>", l_strDateTime);
}
else if (dt.Columns[j].DataType == typeof(string))
{
sb.AppendFormat("<td>{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />"));
}
else
{
sb.AppendFormat("<td>{0}</td>", dt.Rows[i][j]);
}
}
else
{
sb.Append("<td></td>");
}
}
sb.Append("</tr>");
//weather need sub .
if (groupIndex.Length > 0)
{
#region Grouping
bool SubTotal = false;
decimal dectemp;
if (i == dt.Rows.Count - 1)
{
SubTotal = true;
}
else
{
for (int g_i = 0; g_i < groupIndex.Count(); g_i++)
{
if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i + 1][groupIndex[g_i]].ToString())
{
SubTotal = true;
break;
}
}
}
tempRowsCount++;
for (int j = 0; j < subTotalInfo.Length; j++)
{
//v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value
//subTotalInfo[j].total += !Convert.IsDBNull(dt.Rows[i][subTotalInfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString()) : 0M;
if (Decimal.TryParse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString(), out dectemp))
{
subTotalInfo[j].total += dectemp;
}
}
for (int j = 0; j < subAvginfo.Length; j++)
{
//v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value
//subAvginfo[j].total += !Convert.IsDBNull(dt.Rows[i][subAvginfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString()) : 0M;
if (Decimal.TryParse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString(), out dectemp))
{
subAvginfo[j].total += dectemp;
}
}
#region sub total
if (SubTotal && Subsum_Index.Count() > 0)
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subsum_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
sb.Append("<td style='font-weight:bold;'>Total: " + subTotalInfo[array_index].total.ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + subTotalInfo[array_index].total.ToString() + "</td>");
}
subTotalInfo[array_index].total = 0;
}
else
{
if (j == Subsum_Index[0] - 1 && Subsum_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>Total</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#region sub count
if (SubTotal && Subcount_Index.Count() > 0 && !Convert.IsDBNull(dt.Rows[i][groupIndex[0]]) && !string.IsNullOrEmpty(Convert.ToString(dt.Rows[i][groupIndex[0]])))
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subcount_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
sb.Append("<td style='font-weight:bold;'>");
sb.Append(subcountLabel);
sb.Append(": " + tempRowsCount.ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + tempRowsCount.ToString() + "</td>");
}
}
else
{
if (j == Subcount_Index[0] - 1 && Subcount_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>");
sb.Append(subcountLabel);
sb.Append("</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#region sub avg
if (SubTotal && Subavg_Index.Count() > 0)
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subavg_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
if (tempRowsCount > 0)
{
sb.Append("<td style='font-weight:bold;'>Avg: " + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>");
}
}
else
{
if (tempRowsCount > 0)
{
sb.Append("<td style='font-weight:bold;'>" + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>");
}
}
subAvginfo[array_index].total = 0;
}
else
{
if (j == Subavg_Index[0] - 1 && Subavg_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>Avg</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#endregion
}
}
else
{
//showChangeOnly
//v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case( hidden flag seem irrelevant)
//bool hidden = false;
//if (groupIndex.Length > 0 && i > 0)
//{
// int sameCount = 0;
// for (int g_i = 0; g_i < groupIndex.Count(); g_i++)
// {
// if (dt.Rows[i][groupIndex[g_i]].ToString() == dt.Rows[i - 1][groupIndex[g_i]].ToString())
// {
// sameCount++;
// }
// }
// if (sameCount == groupIndex.Length)
// {
// hidden = true;
// }
//}
//v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case
if (groupIndex.Length > 0)
{
#region group header
bool GroupBegin = false;
if (i == 0)
{
GroupBegin = true;
}
else
{
for (int g_i = 0; g_i < groupIndex.Count(); g_i++)
{
if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i - 1][groupIndex[g_i]].ToString())
{
GroupBegin = true;
break;
}
}
}
if (GroupBegin)
{
tempRowsCount = 0;
sb.Append("<tr data-rowtype=\"groupheader\">");
sb.AppendFormat("<td{0}>", dt.Columns.Count != 1 ? String.Format(" colspan=\"{0}\"", dt.Columns.Count) : String.Empty);
StringBuilder sbgrouptitle = new StringBuilder();
//v1.0.0 - Cheong - 2015/05/27 - Use Displayname instead
//sbgrouptitle.AppendFormat("{0} : {1}", group[0], dt.Rows[i][groupIndex[0]]);
//sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]);
if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]))
{
if (dt.Columns[groupIndex[0]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[0]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], l_strDateTime);
}
else
{
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], dt.Rows[i][groupIndex[0]]);
}
}
else
{
sbgrouptitle.AppendFormat("{0} : {1}", content[group[0]], String.Empty);
}
for (int g_i = 1; g_i < groupIndex.Length; g_i++)
{
//v1.0.0 - Cheong - 2015/05/27 - Same as above comment
//sbgrouptitle.AppendFormat(" {0} : {1}", group[g_i], dt.Rows[i][groupIndex[g_i]]);
//sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]);
if (!Convert.IsDBNull(dt.Rows[i][groupIndex[0]]))
{
if (dt.Columns[groupIndex[g_i]].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][groupIndex[g_i]]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], l_strDateTime);
}
else
{
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], dt.Rows[i][groupIndex[g_i]]);
}
}
else
{
sbgrouptitle.AppendFormat(" {0} : {1}", content[group[g_i]], String.Empty);
}
}
sb.Append(sbgrouptitle.ToString());
sb.Append("</td>");
sb.Append("</tr>");
}
#endregion
}
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
if (!Convert.IsDBNull(dt.Rows[i][j]))
{
//v1.0.0 - Cheong - 2016/03/17 - In "Show changed data only" mode, if the first column value is different, show it.
//if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]))) || hidden)
//v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case(hidden flag seem irrelevant)
//if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]) && (dt.Rows[i][0].Equals(dt.Rows[i - 1][0])))) || hidden)
if (((i != 0) && (dt.Rows[i][j].Equals(dt.Rows[i - 1][j]) && (dt.Rows[i][0].Equals(dt.Rows[i - 1][0])))))
{
//v1.1.0 - Cheong - 2016/06/07 - "Show changed data only" will change text color to light grey instead of inserting blank
//sb.Append("<td></td>");
if (dt.Columns[j].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", l_strDateTime);
}
else if (dt.Columns[j].DataType == typeof(string))
{
sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />"));
}
else
{
sb.AppendFormat("<td style=\"color: #C0C0C0\">{0}</td>", dt.Rows[i][j]);
}
}
else
{
//sb.Append("<td>" + dt.Rows[i][j].ToString() + "</td>");
if (dt.Columns[j].DataType == typeof(DateTime))
{
string l_strDateTime = ((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
l_strDateTime = l_strDateTime.Replace("1900-01-01", "").Trim();
l_strDateTime = l_strDateTime.Replace("00:00:00", "").Trim();
sb.AppendFormat("<td>{0}</td>", l_strDateTime);
}
else if (dt.Columns[j].DataType == typeof(string))
{
sb.AppendFormat("<td>{0}</td>", ((string)dt.Rows[i][j]).Replace("\r", "").Replace("\n", "<br />"));
}
else
{
sb.AppendFormat("<td>{0}</td>", dt.Rows[i][j]);
}
}
}
else
{
sb.Append("<td></td>");
}
}
sb.Append("</tr>");
//v1.2.0 Kim 2016.11.16 add grouping logic to show changed only case
//weather need sub .
if (groupIndex.Length > 0)
{
#region Grouping
bool SubTotal = false;
decimal dectemp;
if (i == dt.Rows.Count - 1)
{
SubTotal = true;
}
else
{
for (int g_i = 0; g_i < groupIndex.Count(); g_i++)
{
if (dt.Rows[i][groupIndex[g_i]].ToString() != dt.Rows[i + 1][groupIndex[g_i]].ToString())
{
SubTotal = true;
break;
}
}
}
tempRowsCount++;
for (int j = 0; j < subTotalInfo.Length; j++)
{
//v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value
//subTotalInfo[j].total += !Convert.IsDBNull(dt.Rows[i][subTotalInfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString()) : 0M;
if (Decimal.TryParse(dt.Rows[i][subTotalInfo[j].ColumnIndex].ToString(), out dectemp))
{
subTotalInfo[j].total += dectemp;
}
}
for (int j = 0; j < subAvginfo.Length; j++)
{
//v1.1.0 - Cheong - 2016/05/27 - Do not throw exception when attempt to sum a field that can have null value
//subAvginfo[j].total += !Convert.IsDBNull(dt.Rows[i][subAvginfo[j].ColumnIndex]) ? Decimal.Parse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString()) : 0M;
if (Decimal.TryParse(dt.Rows[i][subAvginfo[j].ColumnIndex].ToString(), out dectemp))
{
subAvginfo[j].total += dectemp;
}
}
#region sub total
if (SubTotal && Subsum_Index.Count() > 0)
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subsum_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
sb.Append("<td style='font-weight:bold;'>Total: " + subTotalInfo[array_index].total.ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + subTotalInfo[array_index].total.ToString() + "</td>");
}
subTotalInfo[array_index].total = 0;
}
else
{
if (j == Subsum_Index[0] - 1 && Subsum_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>Total</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#region sub count
if (SubTotal && Subcount_Index.Count() > 0)
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subcount_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
sb.Append("<td style='font-weight:bold;'>Count: " + tempRowsCount.ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + tempRowsCount.ToString() + "</td>");
}
}
else
{
if (j == Subcount_Index[0] - 1 && Subcount_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>Count</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#region sub avg
if (SubTotal && Subavg_Index.Count() > 0)
{
sb.Append("<tr>");
for (int j = 0; j < columnsCount; j++)
{
if (hideIndex.Contains(j))
{
continue;
}
int array_index = Array.IndexOf(Subavg_Index, j);
if (array_index >= 0)
{
if (j == 0)
{
if (tempRowsCount > 0)
{
sb.Append("<td style='font-weight:bold;'>Avg: " + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>");
}
}
else
{
if (tempRowsCount > 0)
{
sb.Append("<td style='font-weight:bold;'>" + (subAvginfo[array_index].total / tempRowsCount).ToString("#.##") + "</td>");
}
}
subAvginfo[array_index].total = 0;
}
else
{
if (j == Subavg_Index[0] - 1 && Subavg_Index[0] != 0)
{
sb.Append("<td style='font-weight:bold;'>Avg</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion
#endregion
}
}
}
#region rp sum,avg,count
IList <decimal> sum_result = new List <decimal>(sum_Index.Length);
IList <decimal> avg_result = new List <decimal>(avg_Index.Length);
for (int j = 0; j < sum_Index.Length; j++)
{
sum_result.Add(0);
}
for (int j = 0; j < avg_Index.Length; j++)
{
avg_result.Add(0);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
decimal _tmp;
for (int j = 0; j < sum_Index.Length; j++)
{
if (Decimal.TryParse(dt.Rows[i][sum_Index[j]].ToString(), out _tmp))
{
sum_result[j] += !Convert.IsDBNull(dt.Rows[i][sum_Index[j]]) ? _tmp : 0M;
}
}
for (int j = 0; j < avg_Index.Length; j++)
{
if (Decimal.TryParse(dt.Rows[i][avg_Index[j]].ToString(), out _tmp))
{
avg_result[j] += !Convert.IsDBNull(dt.Rows[i][avg_Index[j]]) ? _tmp : 0M;
}
}
}
#region Total
if (sum_Index.Length > 0)
{
sb.Append("<tr data-rowtype=\"summary\">");
for (int i = 0; i < columnsCount; i++)
{
if (hideIndex.Contains(i))
{
continue;
}
int contain_index = Array.IndexOf(sum_Index, i);
if (contain_index >= 0)
{
if (sum_Index[0] == 0 && i == 0)
{
sb.Append("<td style='font-weight:bold;'>Report Total:" + (sum_result[0]).ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + (sum_result[contain_index]).ToString() + "</td>");
}
}
else
{
if (sum_Index[0] != 0 && i == sum_Index[0] - 1)
{
sb.Append("<td style='font-weight:bold;'>Report Total</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion Total
#region Count
if (count_Index.Length > 0)
{
sb.Append("<tr data-rowtype=\"summary\">");
for (int i = 0; i < columnsCount; i++)
{
if (hideIndex.Contains(i))
{
continue;
}
int contain_index = Array.IndexOf(count_Index, i);
if (contain_index >= 0)
{
if (count_Index[0] == 0 && i == 0)
{
sb.Append("<td style='font-weight:bold;'>Report Count:" + dt.Rows.Count.ToString() + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>" + dt.Rows.Count.ToString() + "</td>");
}
}
else
{
if (count_Index[0] != 0 && i == count_Index[0] - 1)
{
sb.Append("<td style='font-weight:bold;'>Report Count</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion Count
#region Avg
if (avg_Index.Length > 0)
{
sb.Append("<tr data-rowtype=\"summary\">");
for (int i = 0; i < columnsCount; i++)
{
if (hideIndex.Contains(i))
{
continue;
}
int contain_index = Array.IndexOf(avg_Index, i);
if (contain_index >= 0)
{
if (avg_Index[0] == 0 && i == 0)
{
if (dt.Rows.Count > 0)
{
sb.Append("<td style='font-weight:bold;'>Report Avg:" + (avg_result[0] / dt.Rows.Count).ToString("#.##") + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>Report Avg:0</td>");
}
}
else
{
if (dt.Rows.Count > 0)
{
sb.Append("<td style='font-weight:bold;'>" + (avg_result[contain_index] / dt.Rows.Count).ToString("#.##") + "</td>");
}
else
{
sb.Append("<td style='font-weight:bold;'>Report Avg:0</td>");
}
}
}
else
{
if (avg_Index[0] != 0 && i == avg_Index[0] - 1)
{
sb.Append("<td style='font-weight:bold;'>Report Avg</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
sb.Append("</tr>");
}
#endregion Avg
#endregion rp sum,avg,count
sb.Append("</table>");
return(sb.ToString());
}