private void ShowColumnName()
{
shtQueryList.RowCount = 0;
for (int i = 0; i < m_SheetViewData.ColumnCount; i++)
{
if (m_SheetViewData.Columns[i].Visible == true)
{
int iRowCount = shtQueryList.RowCount++;
shtQueryList.Cells[iRowCount, (int)eCol.ColumnName].Text = m_SheetViewData.ColumnHeader.Cells[0, i].Text;
shtQueryList.Cells[iRowCount, (int)eCol.ColumnIndex].Text = i.ToString();
ICellType cellType = m_SheetViewData.GetCellType(0, i);
if (cellType is NumberCellType)
{
shtQueryList.Cells[iRowCount, (int)eCol.ColumnType].Value = eColumnType.Number;
shtQueryList.Cells[iRowCount, (int)eCol.Sign].Value = eSign.Between.ToString();
shtQueryList.Cells[iRowCount, (int)eCol.Value1].CellType = CtrlUtil.CreateNumberCellType();
shtQueryList.Cells[iRowCount, (int)eCol.Value2].CellType = CtrlUtil.CreateNumberCellType();
}
else if (cellType is DateTimeCellType)
{
shtQueryList.Cells[iRowCount, (int)eCol.ColumnType].Value = eColumnType.DateTime;
shtQueryList.Cells[iRowCount, (int)eCol.Sign].Value = eSign.Between.ToString();
shtQueryList.Cells[iRowCount, (int)eCol.Value1].CellType = CtrlUtil.CreateDateTimeCellType();
shtQueryList.Cells[iRowCount, (int)eCol.Value2].CellType = CtrlUtil.CreateDateTimeCellType();
}
//else
//{
// shtQueryList.Cells[iRowCount, (int)eCol.ColumnType].Value = eColumnType.String;
// shtQueryList.Cells[iRowCount, (int)eCol.Sign].Value = eSign.Like.ToString();
// shtQueryList.Cells[iRowCount, (int)eCol.Value1].CellType = CtrlUtil.CreateTextCellType();
// shtQueryList.Cells[iRowCount, (int)eCol.Value2].Locked = true;
// shtQueryList.Cells[iRowCount, (int)eCol.Value2].BackColor = Color.LightGray;
//}
else if (m_SheetViewData.ColumnHeader.Cells[0, i].Text != DataDefine.MASTER_NO_FIELD_NAME)
{
shtQueryList.Cells[iRowCount, (int)eCol.ColumnType].Value = eColumnType.String;
shtQueryList.Cells[iRowCount, (int)eCol.Sign].Value = eSign.Like.ToString();
shtQueryList.Cells[iRowCount, (int)eCol.Value1].CellType = CtrlUtil.CreateTextCellType();
shtQueryList.Cells[iRowCount, (int)eCol.Value2].Locked = true;
shtQueryList.Cells[iRowCount, (int)eCol.Value2].BackColor = Color.LightGray;
}
// Add by Pongthorn S. @ 2012-05-18
if (m_SheetViewData.ColumnHeader.Cells[0, i].Text == DataDefine.MASTER_NO_FIELD_NAME)
{
shtQueryList.Cells[iRowCount, (int)eCol.ColumnType].Value = eColumnType.String;
shtQueryList.Cells[iRowCount, (int)eCol.Sign].Value = eSign.Between.ToString();
shtQueryList.Cells[iRowCount, (int)eCol.Value1].CellType = CtrlUtil.CreateTextCellType();
shtQueryList.Cells[iRowCount, (int)eCol.Value2].CellType = CtrlUtil.CreateTextCellType();
}
}
}
}
/// <summary>
/// Get format string for Excel's cell.
/// </summary>
/// <param name="cellType">Specific CellType</param>
/// <returns></returns>
private string GetExcelCellFormatString(ICellType cellType)
{
string strFormat = "";
if (cellType is NumberCellType)
{
NumberCellType numberCellType = (NumberCellType)cellType;
if (numberCellType.DecimalPlaces < 0)
{
strFormat = "#,##0.00";
}
else if (numberCellType.DecimalPlaces == 0)
{
if (numberCellType.ShowSeparator)
{
strFormat = "#,##0";
}
else
{
strFormat = "#0";
}
}
else
{
string decimalPlace = "";
decimalPlace = decimalPlace.PadLeft(numberCellType.DecimalPlaces, '0');
strFormat = "#,##0." + decimalPlace;
}
}
else if (cellType is DateTimeCellType)
{
DateTimeCellType dateTimeCellType = (DateTimeCellType)cellType;
if (dateTimeCellType.UserDefinedFormat == String.Empty)
{
strFormat = CommonLib.Common.CurrentUserInfomation.DateFormat.ToString();// CommonLib.Common.CurrentUserInfomation.DateFormat.ToString();
}
else
{
strFormat = dateTimeCellType.UserDefinedFormat;
}
}
else
{
strFormat = "@";
}
return(strFormat);
}
public static ICellType CreateInstance(FormatInfo Format)
{
ICellType r = null;
if (Format.Style == FormatStyle.Currency ||
Format.Style == FormatStyle.General ||
Format.Style == FormatStyle.Number ||
Format.Style == FormatStyle.Percent ||
Format.Style == FormatStyle.ScientificCount)
{
FarPoint.Win.Spread.CellType.GeneralCellType rs = new FarPoint.Win.Spread.CellType.GeneralCellType();
rs.FormatString = Format.Format;
rs.WordWrap = true;
r = rs;
return(r);
}
else if (Format.Style == FormatStyle.Date ||
Format.Style == FormatStyle.Time)
{
DateTimeCellType ds = new DateTimeCellType();
ds.UserDefinedFormat = Format.Format;
r = ds;
return(r);
}
else if (Format.Style == FormatStyle.Text)
{
FarPoint.Win.Spread.CellType.TextCellType rs = new FarPoint.Win.Spread.CellType.TextCellType();
rs.Multiline = true;
rs.WordWrap = true;
r = rs;
return(r);
}
else
{
FarPoint.Win.Spread.CellType.GeneralCellType gs = new FarPoint.Win.Spread.CellType.GeneralCellType();
gs.FormatString = Format.Format;
r = gs;
return(r);
}
}
private void ExportExcel(string filename)
{
if (FileIsLocked(filename))
{
throw new ApplicationException("File is used by another program.");
}
ExcelControl xlApp = new ExcelControl();
xlApp.Hide();
bool bHeader = chkExcel_Header.Checked;
//int iCurrentRowIndex = 0;
FarPoint.Win.Spread.SheetView sht = m_spread.ActiveSheet;
//คำนวณค่าของ MaxProgress ทั้งหมด
prgExport.Maximum = sht.RowCount + 1;
prgExport.Value = 1;
//Current excel column
int iCurrentExcelColumn = 0;
int iTotalExcelColumn = 0;
List <int> lastExportcolumn = new List <int>();
//#################
//ColumnHeader :: Adjust column-width
//#################
for (int i = 0; i < sht.Columns.Count; i++)
{
if (sht.Columns[i].StyleName == "NO_EXPORT")
{
continue;
}
if (sht.Columns[i].StyleName == "EXPORT_LAST")
{
lastExportcolumn.Add(i);
continue;
}
//Adjust column-width
//xlApp.SetColumnWidth(i + 1, i + 1, Convert.ToInt32(sht.Columns[i].Width));
xlApp.SetColumnWidth(iCurrentExcelColumn + 1, iCurrentExcelColumn + 1, Convert.ToInt32(sht.Columns[i].Width));
iCurrentExcelColumn = iCurrentExcelColumn + 1;
iTotalExcelColumn = iTotalExcelColumn + 1;
}
//Add export_last column
foreach (int i in lastExportcolumn)
{
xlApp.SetColumnWidth(iCurrentExcelColumn + 1, iCurrentExcelColumn + 1, Convert.ToInt32(sht.Columns[i].Width));
iCurrentExcelColumn = iCurrentExcelColumn + 1;
iTotalExcelColumn = iTotalExcelColumn + 1;
}
//#################
// ColumnHeader :: ColSpan && RowSpan
//#################
if (bHeader)
{
for (int i = 0; i < sht.ColumnHeader.RowCount; i++)
{
iCurrentExcelColumn = 0;
for (int j = 0; j < sht.ColumnHeader.Columns.Count; j++)
{
if (sht.Columns[j].StyleName == "NO_EXPORT" || sht.Columns[j].StyleName == "EXPORT_LAST")
{
continue;
}
//WriteText
if (sht.ColumnHeader.Cells[i, j].Text == "")
{
//xlApp.WriteCellText(i + 1, j + 1, sht.ColumnHeader.Cells[i, j].Column.Label);
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Column.Label);
}
else
{
//xlApp.WriteCellText(i + 1, j + 1, sht.ColumnHeader.Cells[i, j].Text);
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Text);
}
//Set alignment ColumnHeader
XL.XlHAlign hAlign = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XL.XlVAlign vAlign = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
GetColumnHeaderAlignment(sht.ColumnHeader.Cells[i, j], out hAlign, out vAlign);
//xlApp.SetAlignment(i + 1, j + 1, i + 1, j + 1, hAlign, vAlign);
xlApp.SetAlignment(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, hAlign, vAlign);
//xlApp.SetBackgroundColor(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, Color.FromArgb(250, 206, 135));
//xlApp.SetFontBold(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, true);
//Set ColumnSpan and RowSpan
if (sht.ColumnHeader.Cells[i, j].ColumnSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + 1, j + sht.ColumnHeader.Cells[i, j].ColumnSpan);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + sht.ColumnHeader.Cells[i, j].ColumnSpan);
j += sht.ColumnHeader.Cells[i, j].ColumnSpan - 1;
}
if (sht.ColumnHeader.Cells[i, j].RowSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, j + 1);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, iCurrentExcelColumn + 1);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set label to export_last column
foreach (int j in lastExportcolumn)
{
//WriteText
if (sht.ColumnHeader.Cells[i, j].Text == "")
{
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Column.Label);
}
else
{
xlApp.WriteCellText(i + 1, iCurrentExcelColumn + 1, "'" + sht.ColumnHeader.Cells[i, j].Text);
}
//Set alignment ColumnHeader
XL.XlHAlign hAlign = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XL.XlVAlign vAlign = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
GetColumnHeaderAlignment(sht.ColumnHeader.Cells[i, j], out hAlign, out vAlign);
xlApp.SetAlignment(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, hAlign, vAlign);
//xlApp.SetBackgroundColor(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, Color.FromArgb(250, 206, 135));
//xlApp.SetFontBold(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + 1, true);
//Set ColumnSpan and RowSpan
if (sht.ColumnHeader.Cells[i, j].ColumnSpan > 1)
{
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + 1, iCurrentExcelColumn + sht.ColumnHeader.Cells[i, j].ColumnSpan);
//j += sht.ColumnHeader.Cells[i, j].ColumnSpan - 1;
}
if (sht.ColumnHeader.Cells[i, j].RowSpan > 1)
{
//xlApp.MergeCell(i + 1, j + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, j + 1);
xlApp.MergeCell(i + 1, iCurrentExcelColumn + 1, i + sht.ColumnHeader.Cells[i, j].RowSpan, iCurrentExcelColumn + 1);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
xlApp.SetRowHeight(i + 1, i + 1, 200);
xlApp.SetBackgroundColor(i + 1, 1, i + 1, iCurrentExcelColumn, Color.FromArgb(250, 206, 135));
xlApp.SetFontBold(i + 1, 1, i + 1, iCurrentExcelColumn, true);
xlApp.SetWrapText(i + 1, 1, i + 1, iCurrentExcelColumn);
}
//Microsoft.Office.Interop.Excel.Range range = xlApp.ExcelApplication.get_Range(
// "A1:F1"
// //+ (sht.RowCount + sht.ColumnHeader.RowCount).ToString()
// //, xlApp.GetColumnChar(sht.ColumnHeader.Columns.Count)
// // + (sht.RowCount + sht.ColumnHeader.RowCount).ToString()
//);
//range.Interior.Color = System.Drawing.Color.Moccasin.ToArgb();
//range.Font.Bold = true;
}
//if (bHeader)
//{
// //Update FormatText for ColumnHeader = "@"
// xlApp.FormatRow(1, sht.ColumnHeader.RowCount, "@");
// //Update start row index.
// iCurrentRowIndex = sht.ColumnHeader.RowCount;
//}
//#################
// Draw DataContent
//#################
//for (int i = 0; i < sht.Rows.Count; i++)
//{
// for (int j = 0; j < sht.Columns.Count; j++)
// {
// ICellType cellType = sht.GetCellType(i, j);
// string strFormat = GetExcelCellFormatString(cellType);
// if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
// {
// if (String.IsNullOrEmpty(strFormat))
// strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
// if (sht.Cells[i, j].Value != null)
// {
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat),strFormat);
// }
// }
// else // Another CellType.
// {
// string strOutput = String.Empty;
// if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
// }
// }
// prgExport.Value += 1;
// prgExport.Refresh();
// iCurrentRowIndex++;
//}
//xlApp.DeclareDataContent(sht.ColumnHeader.Rows.Count, sht.Rows.Count, sht.Columns.Count);
xlApp.DeclareDataContent(sht.ColumnHeader.Rows.Count, sht.Rows.Count, iTotalExcelColumn);
//check row count แล้วค่อย set format เพราะว่าข้างในมันต้องใช้ cell type ไป checkว่าเป็น typeอะไร
if (sht.Rows.Count > 0)
{
iCurrentExcelColumn = 0;
for (int iSetColFormat = 0; iSetColFormat < sht.Columns.Count; iSetColFormat++)
{
if (sht.Columns[iSetColFormat].StyleName == "NO_EXPORT" || sht.Columns[iSetColFormat].StyleName == "EXPORT_LAST")
{
continue;
}
ICellType cellType = sht.GetCellType(0, iSetColFormat);
string strFormat = GetExcelCellFormatString(cellType);
if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
{
if (String.IsNullOrEmpty(strFormat))
{
strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
}
//if (sht.Cells[i, j].Value != null)
//{
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat), strFormat);
//}
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
//xlApp.SetColumnFormat(iSetColFormat + 1, strFormat);
}
else // Another CellType.
{
//string strOutput = String.Empty;
//if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
//xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
//xlApp.SetColumnFormat(iSetColFormat + 1, strFormat);
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set format to export_last column
foreach (int iSetColFormat in lastExportcolumn)
{
ICellType cellType = sht.GetCellType(0, iSetColFormat);
string strFormat = GetExcelCellFormatString(cellType);
if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
{
if (String.IsNullOrEmpty(strFormat))
{
strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;
}
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
else // Another CellType.
{
xlApp.SetColumnFormat(iCurrentExcelColumn + 1, strFormat);
}
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
}
int iVisibledRowCount = 0;
for (int i = 0; i < sht.Rows.Count; i++)
{
//modify by Bunyapat L. on 29 Jun 2011
//export เฉพาะ row ที่แสดงผลเท่านั้น
if (sht.GetRowVisible(i))
{
iCurrentExcelColumn = 0;
for (int j = 0; j < sht.Columns.Count; j++)
{
//ICellType cellType = sht.GetCellType(i, j);
////set format ของ column
//string strFormat = GetExcelCellFormatString(cellType);
//if (cellType is DateTimeCellType) // Check CellType of current cell for DateTime type.
//{
// if (String.IsNullOrEmpty(strFormat))
// strFormat = CommonLib.Common.CurrentUserInfomation.DateFormatString;//.ToString();
// if (sht.Cells[i, j].Value != null)
// {
// DateTime dateTime = Convert.ToDateTime(sht.Cells[i, j].Value);
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, dateTime.ToString(strFormat), strFormat);
// }
//}
//else // Another CellType.
//{
// string strOutput = String.Empty;
// if (sht.Cells[i, j].Value != null)
// strOutput = sht.Cells[i, j].Value.ToString();
// xlApp.WriteText(iCurrentRowIndex + 1, j + 1, strOutput, strFormat);
//}
if (sht.Columns[j].StyleName == "NO_EXPORT" || sht.Columns[j].StyleName == "EXPORT_LAST")
{
continue;
}
//xlApp.WriteRawData(iVisibledRowCount, j, sht.Cells[i, j].Value);
xlApp.WriteRawData(iVisibledRowCount, iCurrentExcelColumn, sht.Cells[i, j].Value);
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
//Set data to export_last column
foreach (int j in lastExportcolumn)
{
xlApp.WriteRawData(iVisibledRowCount, iCurrentExcelColumn, sht.Cells[i, j].Value);
iCurrentExcelColumn = iCurrentExcelColumn + 1;
}
iVisibledRowCount++;
}
prgExport.Value += 1;
prgExport.Refresh();
//iCurrentRowIndex++;
}
//set alignment of detail
xlApp.SetVerticalAlignment(sht.ColumnHeader.Rows.Count + 1, 1, iVisibledRowCount + 1, iTotalExcelColumn, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop);
//xlApp.ResizeDataContent(sht.ColumnHeader.Rows.Count, iVisibledRowCount, sht.Columns.Count);
xlApp.ResizeDataContent(sht.ColumnHeader.Rows.Count, iVisibledRowCount, iTotalExcelColumn);
//#################
// Save to file.
//#################
xlApp.SaveAs(filename);
// Close Excel Application.
xlApp.Hide();
xlApp.Dispose();
}
public static ICellType CreateCellType(string CellType)
{
ICellType Result = null;
switch (CellType.ToLower())
{
case "文本":
Result = new TextCellType();
break;
case "数字":
Result = new NumberCellType();
break;
case "百分号":
Result = new PercentCellType();
break;
case "图片":
Result = new ImageCellType();
break;
case "超链接":
Result = new HyperLinkCellType();
break;
case "货币":
Result = new CurrencyCellType();
break;
case "日期时间":
Result = new DateTimeCellType();
break;
case "复选框":
Result = new CheckBoxCellType();
break;
case "上下标":
Result = new RichTextCellType();
break;
case "条形码":
Result = new BarCodeCellType();
break;
case "下拉框":
Result = new DownListCellType();
break;
case "掩码":
Result = new MaskCellType();
break;
case "删除线":
Result = new DeleteLineCellType();
break;
default:
Result = new TextCellType();
break;
}
return(Result);
}
