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); }