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