EnableControls() { AssertValid(); ExcelColumnFormat eSelectedVertexColumnFormat = this.SelectedVertexColumnFormat; Boolean bEnableMinimumValueToAddTextBox = false; SimpleDateTimeFormat eSimpleDateTimeFormat = SimpleDateTimeFormat.Date; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Other: case ExcelColumnFormat.Number: bEnableMinimumValueToAddTextBox = true; break; case ExcelColumnFormat.Date: break; case ExcelColumnFormat.Time: eSimpleDateTimeFormat = SimpleDateTimeFormat.Time; break; case ExcelColumnFormat.DateAndTime: eSimpleDateTimeFormat = SimpleDateTimeFormat.DateAndTime; break; default: Debug.Assert(false); break; } // Note that the txbMinimumValueToAdd TextBox and the // dtpMinimumValueToAdd DateTimePicker have the same size and location. // Only one is visible at any given time. txbMinimumValueToAdd.Enabled = txbMinimumValueToAdd.Visible = bEnableMinimumValueToAddTextBox; dtpMinimumValueToAdd.Enabled = dtpMinimumValueToAdd.Visible = !bEnableMinimumValueToAddTextBox; dtpMinimumValueToAdd.SimpleFormat = eSimpleDateTimeFormat; btnRemoveSelected.Enabled = (lbxMinimumValues.SelectedIndices.Count > 0); btnRemoveAll.Enabled = (lbxMinimumValues.Items.Count > 0); grpMinimumValues.Enabled = !String.IsNullOrEmpty(cbxVertexColumnName.Text) && (eSelectedVertexColumnFormat != ExcelColumnFormat.Other); }
DateTimeToString ( Int64 ticks, ExcelColumnFormat format ) { AssertValid(); return(DateTimeToString(new DateTime(ticks), format)); }
DateTimeToString ( DateTime dateTime, ExcelColumnFormat format ) { AssertValid(); return(DateTimeToString(dateTime, format, CultureInfo.CurrentCulture)); }
DateTimeToStringLocale1033 ( DateTime dateTime, ExcelColumnFormat format ) { AssertValid(); return(DateTimeToString(dateTime, format, CultureInfo.GetCultureInfo(1033))); }
//************************************************************************* // Constructor: DateTimeFilterParameters() // /// <summary> /// Initializes a new instance of the <see /// cref="DateTimeFilterParameters" /> class. /// </summary> /// /// <param name="columnName"> /// Name of the column that can be filtered on. /// </param> /// /// <param name="minimumCellValue"> /// Minimum cell value in the column, in Excel's date/time format. Sample: /// 39448.583333 (1/1/2008, 2 PM). /// </param> /// /// <param name="maximumCellValue"> /// Maximum cell value in the column, in Excel's date/time format. /// </param> /// /// <param name="format"> /// The column format. /// </param> //************************************************************************* public DateTimeFilterParameters( String columnName, Double minimumCellValue, Double maximumCellValue, ExcelColumnFormat format ) : base(columnName, minimumCellValue, maximumCellValue, 0) { m_eFormat = format; AssertValid(); }
//************************************************************************* // Constructor: DateTimeFilterParameters() // /// <summary> /// Initializes a new instance of the <see /// cref="DateTimeFilterParameters" /> class. /// </summary> /// /// <param name="columnName"> /// Name of the column that can be filtered on. /// </param> /// /// <param name="minimumCellValue"> /// Minimum cell value in the column, in Excel's date/time format. Sample: /// 39448.583333 (1/1/2008, 2 PM). /// </param> /// /// <param name="maximumCellValue"> /// Maximum cell value in the column, in Excel's date/time format. /// </param> /// /// <param name="format"> /// The column format. /// </param> //************************************************************************* public DateTimeFilterParameters ( String columnName, Double minimumCellValue, Double maximumCellValue, ExcelColumnFormat format ) : base(columnName, minimumCellValue, maximumCellValue, 0) { m_eFormat = format; AssertValid(); }
/// <summary> /// Applies the format to range. /// </summary> /// <param name="range">The range.</param> /// <param name="format">The format.</param> private static void ApplyFormatToRange(Excel.Range range, ExcelColumnFormat format) { switch (format) { case ExcelColumnFormat.Accounting: range.NumberFormat = @"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"; break; case ExcelColumnFormat.Currency: range.NumberFormat = "$#,##0.00"; break; case ExcelColumnFormat.Fraction: range.NumberFormat = "# ?/?"; break; case ExcelColumnFormat.LongDate: range.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"; break; case ExcelColumnFormat.Number: range.NumberFormat = "0.00"; break; case ExcelColumnFormat.Percentage: range.NumberFormat = "0.00%"; break; case ExcelColumnFormat.Scientific: range.NumberFormat = "0.00E+00"; break; case ExcelColumnFormat.ShortDate: range.NumberFormat = "m/d/yyyy"; break; case ExcelColumnFormat.Text: range.NumberFormat = "@"; break; case ExcelColumnFormat.Time: range.NumberFormat = "[$-F400]h:mm:ss AM/PM"; break; case ExcelColumnFormat.General: default: range.NumberFormat = "General"; break; } }
GetEdgeTimestampResults ( out String edgeTimestampColumnName, out ExcelColumnFormat edgeTimestampColumnFormat, out Double sourceCalculationNumber1, out Double sourceCalculationNumber2 ) { Debug.Assert(m_eSchemeType == AutoFillSchemeType.EdgeTimestamp); AssertValid(); edgeTimestampColumnName = m_sEdgeTimestampColumnName; edgeTimestampColumnFormat = m_eEdgeTimestampColumnFormat; sourceCalculationNumber1 = m_dEdgeTimestampSourceCalculationNumber1; sourceCalculationNumber2 = m_dEdgeTimestampSourceCalculationNumber2; }
public object ConvertStringToValue(string value, Type type, ExcelColumnFormat format = ExcelColumnFormat.None) { var stringFormat = GetFormatByFormatType(format); object propertyValue = null; if (!string.IsNullOrEmpty(value)) { if (type == typeof(DateTime) || type == typeof(DateTime?)) { propertyValue = value.ToDateFormat(stringFormat); } else if (type == typeof(DateRange)) { propertyValue = value.ToDateRangeFormat(stringFormat); } else if (type == typeof(TimeSpan) || type == typeof(TimeSpan?)) { propertyValue = value.ToTimeFormat(stringFormat); } else if (type == typeof(bool) || type == typeof(bool?)) { propertyValue = value.ToBoolFormat(); } else if (type == typeof(decimal) || type == typeof(decimal?)) { decimal decimalValue; if (decimal.TryParse(value, out decimalValue)) { propertyValue = decimalValue; } } else if (type == typeof(double) || type == typeof(double?)) { double doubleValue; if (double.TryParse(value, out doubleValue)) { propertyValue = doubleValue; } } else { propertyValue = value; } } return(propertyValue); }
SetEdgeTimestampResults ( String edgeTimestampColumnName, ExcelColumnFormat edgeTimestampColumnFormat, Double sourceCalculationNumber1, Double sourceCalculationNumber2 ) { Debug.Assert(!String.IsNullOrEmpty(edgeTimestampColumnName)); AssertValid(); m_eSchemeType = AutoFillSchemeType.EdgeTimestamp; m_sEdgeTimestampColumnName = edgeTimestampColumnName; m_eEdgeTimestampColumnFormat = edgeTimestampColumnFormat; m_dEdgeTimestampSourceCalculationNumber1 = sourceCalculationNumber1; m_dEdgeTimestampSourceCalculationNumber2 = sourceCalculationNumber2; }
public string GetFormatByFormatType(ExcelColumnFormat type) { switch (type) { case ExcelColumnFormat.Date: return(DateTimeExtention.DefaultDateFormat); case ExcelColumnFormat.DateTime: return(DateTimeExtention.DefaultDateTimeFormat); case ExcelColumnFormat.Hours12Time: return(TimeSpanExtention.Time12HourFormat); case ExcelColumnFormat.Hours24Time: return(TimeSpanExtention.DefaultTime24HourFormat); } return(null); }
DateTimeToString ( DateTime dateTime, ExcelColumnFormat format, IFormatProvider formatProvider ) { Debug.Assert(formatProvider != null); AssertValid(); String sToStringFormat = null; switch (format) { case ExcelColumnFormat.Date: sToStringFormat = "d"; break; case ExcelColumnFormat.Time: sToStringFormat = "T"; break; case ExcelColumnFormat.DateAndTime: sToStringFormat = "g"; break; default: Debug.Assert(false); return(null); } return(dateTime.ToString(sToStringFormat, formatProvider)); }
//************************************************************************* // Method: SourceCalculationNumberToTimestampString() // /// <summary> /// Converts a source calculation number to a string for the edge timestamp /// scheme. /// </summary> /// /// <param name="dSourceCalculationNumber"> /// The number to convert. /// </param> /// /// <param name="eEdgeTimestampColumnFormat"> /// The source column format. /// </param> /// /// <returns> /// <paramref name="dSourceCalculationNumber" /> converted to a string. /// </returns> //************************************************************************* protected String SourceCalculationNumberToTimestampString( Double dSourceCalculationNumber, ExcelColumnFormat eEdgeTimestampColumnFormat ) { AssertValid(); switch (eEdgeTimestampColumnFormat) { case ExcelColumnFormat.Date: case ExcelColumnFormat.DateAndTime: // The calculation numbers are in ticks. return ( ExcelDateTimeUtil.DateTimeToString( (Int64)dSourceCalculationNumber, eEdgeTimestampColumnFormat) ); case ExcelColumnFormat.Time: // The calculation numbers are in fractions of a day. return ( ExcelDateTimeUtil.DateTimeToString( (Int64)(dSourceCalculationNumber * TimeSpan.TicksPerDay), ExcelColumnFormat.Time) ); default: // The calculation numbers are actual numbers. In this oddball // case, use an arbitrary number of decimal places. return ( DoubleToString(dSourceCalculationNumber, 2) ); } }
/// <summary> /// Initializes a new instance of the <see cref="ExcelColumnAttribute"/> class. /// </summary> /// <param name="columnTitle">The column heading.</param> /// <param name="columnFormat">The column format.</param> public ExcelColumnAttribute(String columnTitle, ExcelColumnFormat columnFormat = ExcelColumnFormat.General) { this.Title = columnTitle ?? String.Empty; this.Format = columnFormat; }
btnAddMinimumValue_Click ( object sender, EventArgs e ) { AssertValid(); ExcelColumnFormat eSelectedVertexColumnFormat = this.SelectedVertexColumnFormat; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Number: Double dMinimumValue; if (!ValidateDoubleTextBox(txbMinimumValueToAdd, Double.MinValue, Double.MaxValue, "Enter a numeric value.", out dMinimumValue)) { return; } lbxMinimumValues.AddItem <FormattableNumber, Double>( new FormattableNumber(dMinimumValue)); txbMinimumValueToAdd.Focus(); txbMinimumValueToAdd.SelectAll(); return; case ExcelColumnFormat.Date: lbxMinimumValues.AddItem <FormattableDate, DateTime>( new FormattableDate(dtpMinimumValueToAdd.Value)); break; case ExcelColumnFormat.Time: lbxMinimumValues.AddItem <FormattableTime, DateTime>( new FormattableTime(dtpMinimumValueToAdd.Value)); break; case ExcelColumnFormat.DateAndTime: lbxMinimumValues.AddItem <FormattableDateAndTime, DateTime>( new FormattableDateAndTime(dtpMinimumValueToAdd.Value)); break; case ExcelColumnFormat.Other: // This should never occur, because the Add button is disabled // when the column format is Other. Debug.Assert(false); break; default: Debug.Assert(false); break; } dtpMinimumValueToAdd.Focus(); }
//************************************************************************* // Constructor: AutoFillWorkbookWithSchemeResults() // /// <summary> /// Initializes a new instance of the <see /// cref="AutoFillWorkbookWithSchemeResults" /> class. /// </summary> //************************************************************************* public AutoFillWorkbookWithSchemeResults() { m_eSchemeType = AutoFillSchemeType.None; m_sVertexCategoryColumnName = null; m_asVertexCategoryNames = null; m_sEdgeWeightColumnName = null; m_dEdgeWeightSourceCalculationNumber1 = 0; m_dEdgeWeightSourceCalculationNumber2 = 0; m_iEdgeWeightDecimalPlaces = 0; m_sEdgeTimestampColumnName = null; m_eEdgeTimestampColumnFormat = ExcelColumnFormat.Other; m_dEdgeTimestampSourceCalculationNumber1 = 0; m_dEdgeTimestampSourceCalculationNumber2 = 0; AssertValid(); }
DoDataExchange ( Boolean bFromControls ) { if (bFromControls) { // Perform error checking. if (cbxVertexColumnName.Items.Count == 0) { OnInvalidComboBox(cbxVertexColumnName, "There are no columns that can be used."); return(false); } String sVertexColumnName; if (!ValidateRequiredComboBox(cbxVertexColumnName, "Select a column.", out sVertexColumnName) ) { return(false); } // Error checking passed. Transfer the data. m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName = sVertexColumnName; ExcelColumnFormat eSelectedVertexColumnFormat = this.SelectedVertexColumnFormat; m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat = eSelectedVertexColumnFormat; String sMinimumValues = String.Empty; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Number: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString < FormattableNumber, Double>(); break; case ExcelColumnFormat.Date: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString < FormattableDate, DateTime>(); break; case ExcelColumnFormat.Time: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString < FormattableTime, DateTime>(); break; case ExcelColumnFormat.DateAndTime: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString < FormattableDateAndTime, DateTime>(); break; case ExcelColumnFormat.Other: break; default: Debug.Assert(false); break; } m_oGroupByVertexAttributeDialogUserSettings.MinimumValues = sMinimumValues; } else { cbxVertexColumnName.Text = m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName; ExcelColumnFormat eSelectedVertexColumnFormat = m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat; this.SelectedVertexColumnFormat = eSelectedVertexColumnFormat; String sMinimumValues = m_oGroupByVertexAttributeDialogUserSettings.MinimumValues; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Number: lbxMinimumValues.CultureInvariantStringToItems < FormattableNumber, Double>(sMinimumValues); break; case ExcelColumnFormat.Date: lbxMinimumValues.CultureInvariantStringToItems < FormattableDate, DateTime>(sMinimumValues); break; case ExcelColumnFormat.Time: lbxMinimumValues.CultureInvariantStringToItems < FormattableTime, DateTime>(sMinimumValues); break; case ExcelColumnFormat.DateAndTime: lbxMinimumValues.CultureInvariantStringToItems < FormattableDateAndTime, DateTime>(sMinimumValues); break; case ExcelColumnFormat.Other: break; default: Debug.Assert(false); break; } EnableControls(); } return(true); }
DoDataExchange ( Boolean bFromControls ) { if (bFromControls) { // Perform error checking. if (cbxVertexColumnName.Items.Count == 0) { OnInvalidComboBox(cbxVertexColumnName, "There are no columns that can be used."); return (false); } String sVertexColumnName; if ( !ValidateRequiredComboBox(cbxVertexColumnName, "Select a column.", out sVertexColumnName) ) { return (false); } // Error checking passed. Transfer the data. m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName = sVertexColumnName; ExcelColumnFormat eSelectedVertexColumnFormat = this.SelectedVertexColumnFormat; m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat = eSelectedVertexColumnFormat; String sMinimumValues = String.Empty; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Number: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString< FormattableNumber, Double>(); break; case ExcelColumnFormat.Date: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString< FormattableDate, DateTime>(); break; case ExcelColumnFormat.Time: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString< FormattableTime, DateTime>(); break; case ExcelColumnFormat.DateAndTime: sMinimumValues = lbxMinimumValues.ItemsToCultureInvariantString< FormattableDateAndTime, DateTime>(); break; case ExcelColumnFormat.Other: break; default: Debug.Assert(false); break; } m_oGroupByVertexAttributeDialogUserSettings.MinimumValues = sMinimumValues; } else { cbxVertexColumnName.Text = m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName; ExcelColumnFormat eSelectedVertexColumnFormat = m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat; this.SelectedVertexColumnFormat = eSelectedVertexColumnFormat; String sMinimumValues = m_oGroupByVertexAttributeDialogUserSettings.MinimumValues; switch (eSelectedVertexColumnFormat) { case ExcelColumnFormat.Number: lbxMinimumValues.CultureInvariantStringToItems< FormattableNumber, Double>(sMinimumValues); break; case ExcelColumnFormat.Date: lbxMinimumValues.CultureInvariantStringToItems< FormattableDate, DateTime>(sMinimumValues); break; case ExcelColumnFormat.Time: lbxMinimumValues.CultureInvariantStringToItems< FormattableTime, DateTime>(sMinimumValues); break; case ExcelColumnFormat.DateAndTime: lbxMinimumValues.CultureInvariantStringToItems< FormattableDateAndTime, DateTime>(sMinimumValues); break; case ExcelColumnFormat.Other: break; default: Debug.Assert(false); break; } EnableControls(); } return (true); }
//************************************************************************* // Method: GetEdgeTimestampResults() // /// <summary> /// Gets the results of a call to <see /// cref="WorkbookSchemeAutoFiller.AutoFillByEdgeTimestamp" />. /// </summary> /// /// <param name="edgeTimestampColumnName"> /// Where the name of the edge table column containing edge timestamps gets /// stored. /// </param> /// /// <param name="edgeTimestampColumnFormat"> /// Where the format of the edge table column containing edge timestamps /// gets stored. /// </param> /// /// <param name="sourceCalculationNumber1"> /// Where the actual first source number used in the calculations gets /// stored if true is returned. /// </param> /// /// <param name="sourceCalculationNumber2"> /// Where the actual second source number used in the calculations gets /// stored if true is returned. /// </param> /// /// <remarks> /// Call this only if <see cref="Type" /> returns <see /// cref="AutoFillSchemeType.EdgeTimestamp" />. /// </remarks> //************************************************************************* public void GetEdgeTimestampResults( out String edgeTimestampColumnName, out ExcelColumnFormat edgeTimestampColumnFormat, out Double sourceCalculationNumber1, out Double sourceCalculationNumber2 ) { Debug.Assert(m_eSchemeType == AutoFillSchemeType.EdgeTimestamp); AssertValid(); edgeTimestampColumnName = m_sEdgeTimestampColumnName; edgeTimestampColumnFormat = m_eEdgeTimestampColumnFormat; sourceCalculationNumber1 = m_dEdgeTimestampSourceCalculationNumber1; sourceCalculationNumber2 = m_dEdgeTimestampSourceCalculationNumber2; }
//************************************************************************* // Method: DateTimeToString() // /// <overloads> /// Converts a DateTime to a string. /// </overloads> /// /// <summary> /// Converts a DateTime to a string using the current culture. /// </summary> /// /// <param name="dateTime"> /// The DateTime to convert. /// </param> /// /// <param name="format"> /// The format to use. Must be <see cref="ExcelColumnFormat.Date" />, /// <see cref="ExcelColumnFormat.Time" />, or <see /// cref="ExcelColumnFormat.DateAndTime" />. /// </param> /// /// <returns> /// <paramref name="dateTime" /> converted to a string in the <paramref /// name="format" /> format. /// </returns> /// /// <remarks> /// This method can be used to convert DateTimes in Excel applications to a /// String using consistent string formats. /// </remarks> //************************************************************************* public static String DateTimeToString( DateTime dateTime, ExcelColumnFormat format ) { AssertValid(); return ( DateTimeToString( dateTime, format, CultureInfo.CurrentCulture) ); }
//************************************************************************* // Method: SetEdgeTimestampResults() // /// <summary> /// Stores the results of a call to <see /// cref="WorkbookSchemeAutoFiller.AutoFillByEdgeTimestamp" />. /// </summary> /// /// <param name="edgeTimestampColumnName"> /// The name of the edge table column containing edge timestamps. /// </param> /// /// <param name="edgeTimestampColumnFormat"> /// The format of the edge table column containing edge timestamps. /// </param> /// /// <param name="sourceCalculationNumber1"> /// The actual first source number used in the calculations. /// </param> /// /// <param name="sourceCalculationNumber2"> /// The actual second source number used in the calculations. /// </param> //************************************************************************* public void SetEdgeTimestampResults( String edgeTimestampColumnName, ExcelColumnFormat edgeTimestampColumnFormat, Double sourceCalculationNumber1, Double sourceCalculationNumber2 ) { Debug.Assert( !String.IsNullOrEmpty(edgeTimestampColumnName) ); AssertValid(); m_eSchemeType = AutoFillSchemeType.EdgeTimestamp; m_sEdgeTimestampColumnName = edgeTimestampColumnName; m_eEdgeTimestampColumnFormat = edgeTimestampColumnFormat; m_dEdgeTimestampSourceCalculationNumber1 = sourceCalculationNumber1; m_dEdgeTimestampSourceCalculationNumber2 = sourceCalculationNumber2; }
public static Stream DataTableToExcel(string fileName, DataTable data, List <ExcelColumnFormat> colFmt, string cols, Func <object, string, object> tranfunc = null, bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null) { if (data.Rows.Count == 0) { return(null); } //data为空,直接退出 int i = 0; int j = 0; int count = 0; IWorkbook workbook = null; ISheet sheet = null; NPOIMemoryStream ms = null; try { //转换对齐 Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment => { switch (alignment) { case System.Drawing.ContentAlignment.BottomRight: case System.Drawing.ContentAlignment.MiddleRight: case System.Drawing.ContentAlignment.TopRight: return(HorizontalAlignment.Right); case System.Drawing.ContentAlignment.BottomCenter: case System.Drawing.ContentAlignment.MiddleCenter: case System.Drawing.ContentAlignment.TopCenter: return(HorizontalAlignment.Center); } return(HorizontalAlignment.Left); }; //设置值的格式 Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) => { string rlt = ""; string vstr = Ass.P.PStr(v); string tv = null; if (tranfunc != null) { tv = Ass.P.PStr(tranfunc(v, colCode)); } ExcelColumnFormat fmt = null; if (tv != null && tv != vstr) { rlt = tv; } else if (colFmt.Any(m => m.ColumnNameCode == colCode)) { rlt = vstr; string v0 = ""; fmt = colFmt.Find(m => m.ColumnNameCode == colCode); if (v != null && fmt.ToStringFormat.IsNotEmpty()) { v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v); if (v0 != vstr) { rlt = v0; } } } else { rlt = vstr; } //设置值 var cell = row.CreateCell(j); var t = rlt.GetStringType(); double dbval = 0; DateTime dtval; if (double.TryParse(rlt, out dbval)) { cell.SetCellValue(dbval); } else if (DateTime.TryParse(rlt, out dtval)) { cell.SetCellValue(rlt); } else { cell.SetCellValue(rlt); } }; using (ms = new NPOIMemoryStream()) { if (!GetWorkbook(fileName, out workbook)) { return(null); } List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合 sheet = workbook.CreateSheet(sheetName); //表标题 if (tableTitle.IsNotEmpty()) { IRow row = sheet.CreateRow(count++); ICell cell = row.CreateCell(0); XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle(); IFont ztFont = workbook.CreateFont(); ztFont.FontHeightInPoints = 14; ztFont.Underline = FontUnderlineType.DoubleAccounting; ztStyle.SetFont(ztFont); ztStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle = ztStyle; cell.SetCellValue(tableTitle); var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1)); //合并单元格 } Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>(); //写入DataTable的列名 if (isColumnWritten == true) { IRow row = sheet.CreateRow(count++); if (colList.Count > 0) //设定了输出的列 { for (j = 0; j < colList.Count(); j++) { var key = colList[j]; var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key; var cell = row.CreateCell(j); var cellfmt = colFmt.Find(m => m.ColumnNameCode == key); cell.SetCellValue(keyname); //设置对齐与数据格式 var cStyle = workbook.CreateCellStyle(); bool bfmt = false; if (cellfmt.Alignment != null) { bfmt = true; cStyle.Alignment = _transHAlignment(cellfmt.Alignment); } if (cellfmt.DataFormat.IsNotEmpty()) { bfmt = true; short?dfmt = null; IDataFormat format = workbook.CreateDataFormat(); switch (cellfmt.DataFormat) { case "Price": dfmt = format.GetFormat("#,##0.00"); break; case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break; case "DateTime": dfmt = 0x16; break; // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break; case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break; } cStyle.DataFormat = dfmt.Value; } if (bfmt) { _columeCellStyles.Add(j, cStyle); } //设置列宽 if (cellfmt.ColumnWidth.HasValue) { sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256)); } } } else { for (j = 0; j < data.Columns.Count; j++) { string key = data.Columns[j].ColumnName; if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文 { var cell = row.CreateCell(j); var cellfmt = colFmt.Find(m => m.ColumnNameCode == key); cell.SetCellValue(cellfmt.ColumnName); //设置对齐 if (cellfmt.Alignment != null) { sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment); } //设置列宽 if (cellfmt.ColumnWidth.HasValue) { sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value); } } else { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } } } } //写入数据 if (colList.Count > 0) { for (i = 0; i < data.Rows.Count; i++, count++) { IRow row = sheet.CreateRow(count); for (j = 0; j < colList.Count; j++) { _val(row, j, data.Rows[i][colList[j]], colList[j]); } } } else { for (i = 0; i < data.Rows.Count; i++, count++) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; j++) { _val(row, j, data.Rows[i][j], data.Columns[j].ColumnName); } } } //设置列格式 foreach (int colIndex in _columeCellStyles.Keys) { sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]); } workbook.Write(ms); //写入到excel ms.Flush(); ms.Position = 0; return(ms); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message); return(null); } }
GetDynamicFilterParameters ( Microsoft.Office.Interop.Excel.Workbook workbook, String worksheetName, String tableName ) { Debug.Assert(workbook != null); Debug.Assert(!String.IsNullOrEmpty(worksheetName)); Debug.Assert(!String.IsNullOrEmpty(tableName)); #if false // For testing. return(GetRandomDynamicFilterParameters(tableName)); #endif LinkedList <DynamicFilterParameters> oDynamicFilterParameters = new LinkedList <DynamicFilterParameters>(); // Get the specified table and loop through its columns. ListObject oTable; if (ExcelUtil.TryGetTable(workbook, worksheetName, tableName, out oTable)) { Application oApplication = workbook.Application; foreach (ListColumn oColumn in oTable.ListColumns) { if (ColumnShouldBeExcluded(oColumn)) { continue; } ExcelColumnFormat eColumnFormat = ExcelUtil.GetColumnFormat(oColumn); switch (eColumnFormat) { case ExcelColumnFormat.Number: case ExcelColumnFormat.Date: case ExcelColumnFormat.Time: case ExcelColumnFormat.DateAndTime: // Get the range of values in the column. Double dMinimumCellValue, dMaximumCellValue; if (TryGetNumericRange(worksheetName, oColumn, out dMinimumCellValue, out dMaximumCellValue)) { if (eColumnFormat == ExcelColumnFormat.Number) { oDynamicFilterParameters.AddLast( new NumericFilterParameters(oColumn.Name, dMinimumCellValue, dMaximumCellValue, ExcelUtil.GetTableColumnDecimalPlaces( oColumn)) ); } else { oDynamicFilterParameters.AddLast( new DateTimeFilterParameters(oColumn.Name, dMinimumCellValue, dMaximumCellValue, eColumnFormat)); } } break; case ExcelColumnFormat.Other: // Skip the column. break; default: Debug.Assert(false); break; } } } return(oDynamicFilterParameters); }
//************************************************************************* // Method: DateTimeToString() // /// <summary> /// Converts a DateTime in ticks to a string using the current culture. /// </summary> /// /// <param name="ticks"> /// The date/time to convert, in 100-nanosecond ticks. /// </param> /// /// <param name="format"> /// The format to use. Must be <see cref="ExcelColumnFormat.Date" />, /// <see cref="ExcelColumnFormat.Time" />, or <see /// cref="ExcelColumnFormat.DateAndTime" />. /// </param> /// /// <returns> /// <paramref name="ticks" /> converted to a string in the /// <paramref name="format" /> format. /// </returns> /// /// <remarks> /// This method can be used to convert DateTimes in Excel applications to a /// String using consistent string formats. /// </remarks> //************************************************************************* public static String DateTimeToString( Int64 ticks, ExcelColumnFormat format ) { AssertValid(); return ( DateTimeToString(new DateTime(ticks), format) ); }
//************************************************************************* // Method: DateTimeToString() // /// <summary> /// Converts a DateTime to a string using a specified format provider. /// </summary> /// /// <param name="dateTime"> /// The DateTime to convert. /// </param> /// /// <param name="format"> /// The format to use. Must be <see cref="ExcelColumnFormat.Date" />, /// <see cref="ExcelColumnFormat.Time" />, or <see /// cref="ExcelColumnFormat.DateAndTime" />. /// </param> /// /// <param name="formatProvider"> /// The IFormatProvider to use. /// </param> /// /// <returns> /// <paramref name="dateTime" /> converted to a string in the <paramref /// name="format" /> format. /// </returns> /// /// <remarks> /// This method can be used to convert DateTimes in Excel applications to a /// String using consistent string formats. /// </remarks> //************************************************************************* public static String DateTimeToString( DateTime dateTime, ExcelColumnFormat format, IFormatProvider formatProvider ) { Debug.Assert(formatProvider != null); AssertValid(); String sToStringFormat = null; switch (format) { case ExcelColumnFormat.Date: sToStringFormat = "d"; break; case ExcelColumnFormat.Time: sToStringFormat = "T"; break; case ExcelColumnFormat.DateAndTime: sToStringFormat = "g"; break; default: Debug.Assert(false); return (null); } return ( dateTime.ToString( sToStringFormat, formatProvider) ); }
//************************************************************************* // Method: DateTimeToStringLocale1033() // /// <summary> /// Converts a DateTime to a string using the locale with the ID 1033. /// </summary> /// /// <param name="dateTime"> /// The DateTime to convert. /// </param> /// /// <param name="format"> /// The format to use. Must be <see cref="ExcelColumnFormat.Date" />, /// <see cref="ExcelColumnFormat.Time" />, or <see /// cref="ExcelColumnFormat.DateAndTime" />. /// </param> /// /// <returns> /// <paramref name="dateTime" /> converted to a string in the <paramref /// name="format" /> format. /// </returns> /// /// <remarks> /// This method can be used to convert DateTimes in Excel applications to a /// String using consistent string formats. /// /// <para> /// Locale ID 1033 must be used when writing to the Excel object model. /// See "Formatting Data in Excel with Various Regional Settings" at /// http://msdn.microsoft.com/en-us/library/ms268748.aspx. /// </para> /// /// </remarks> //************************************************************************* public static String DateTimeToStringLocale1033( DateTime dateTime, ExcelColumnFormat format ) { AssertValid(); return ( DateTimeToString(dateTime, format, CultureInfo.GetCultureInfo(1033) ) ); }