/// <summary>
/// Present number in needed format
/// </summary>
/// <param name="number">Target number</param>
/// <param name="ftype">Type of format</param>
/// <returns>Formated string</returns>
public static string FormatNumber(double number, NumberFormatType ftype)
{
string result = string.Empty;
switch (ftype)
{
case NumberFormatType.Dot:
result = number.ToString(CultureInfo.InvariantCulture);
break;
case NumberFormatType.Exp:
result = number.ToString("0.####E+0");
break;
case NumberFormatType.Bank:
result = number.ToString("0,0.###", CultureInfo.InvariantCulture);
break;
default:
result = number.ToString();
break;
}
return(result);
}
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("Data.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName("Sample");
// Check dates
for (int i = 0; i < 8; i++)
{
for (int j = 0; j < 2; j++)
{
// Set current cell
Cell currentCell = worksheet.Cell(i, j);
// Get format type
NumberFormatType formatType = currentCell.ValueDataTypeByNumberFormatString;
// Write line
Console.Write("Cell({0}:{1}) type is {2}. Value : ", i, j, formatType.ToString());
switch (formatType)
{
case NumberFormatType.DateTime:
{
// Read datetime
DateTime date = currentCell.ValueAsDateTime;
// Write date to console output
Console.Write(date.ToString());
}
break;
case NumberFormatType.General:
{
// Write value to console output
Console.Write(currentCell.Value);
}
break;
}
Console.WriteLine();
}
}
// Close document
document.Close();
Console.ReadKey();
}
public object GetValue(NumberFormatType type)
{
object val = this.GetValue();
if (type != NumberFormatType.DateTime)
{
return(val);
}
DateTime?nullable = ConditionValueConverter.TryDateTime(val);
if (!nullable.HasValue)
{
return(null);
}
return(nullable.Value);
}
public string NumberFormatTest(NumberFormatType type, double number)
{
return(DateAndNumbersFormat.Program.FormatNumber(number, type));
}
internal static void SetRangeFormatType(IWorkbook iWorkbook, int sheetIndex, int startRowIndex, int startColumnIndex, int endRowIndex, int endColIndex, NumberFormatType formatType)
{
string formatString = string.Empty;
try
{
// Get a worksheet reference.
SpreadsheetGear.IWorksheet worksheet = iWorkbook.Worksheets[sheetIndex];
//worksheet.Range.Cells[rowIndex, colIndex].Select();
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].Select();
switch (formatType)
{
case NumberFormatType.Currency:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "$#,##0.00";
break;
case NumberFormatType.Date:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "m/d/yyyy"; ;
break;
case NumberFormatType.DateTime:
case NumberFormatType.Time:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "[$-F400]h:mm:ss AM/PM";
break;
case NumberFormatType.Fraction:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "# ?/?";
break;
case NumberFormatType.General:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "General";
break;
case NumberFormatType.None:
// worksheet.Range.Columns[columnAddress].NumberFormat;
break;
case NumberFormatType.Number:
//worksheet.Range.Cells[rowIndex, colIndex].NumberFormat = "0.00";
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "0.#######";
break;
case NumberFormatType.Percent:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "0.00%";
break;
case NumberFormatType.Scientific:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "0.00E+00";
break;
case NumberFormatType.Text:
worksheet.Range.Cells[startRowIndex, startColumnIndex, endRowIndex, endColIndex].NumberFormat = "@";
break;
default:
break;
}
}
catch (Exception ex)
{
new ApplicationException(ex.ToString());
}
}
public void SetRangeFormatType(int sheetIndex, int startRowIndex, int startColumnIndex, int endRowIndex, int endColIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetRangeFormatType(this.Workbook, sheetIndex, startRowIndex, startColumnIndex, endRowIndex, endColIndex, formatType);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Changes the column format type (eg: text, general, Date, number) for specified column address.
/// </summary>
/// <param name="sheetIndex">sheet index number</param>
/// <param name="columnAddress">column address. foreg: A:A , B:B</param>
/// <param name="formatType">format type.</param>
public void SetColumnFormatType(string columnAddress, int sheetIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetColumnFormatType(this.Workbook, sheetIndex, columnAddress, formatType);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
public void SetCellFormatType(int sheetIndex, int rowIndex, int colIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetCellFormatType(this.Workbook, sheetIndex, rowIndex, colIndex, formatType);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
public NumberFormat(NumberFormatType type)
{
Type = type;
Custom = null;
}
public void SetRangeFormatType(int sheetIndex, int startRowIndex, int startColumnIndex, int endRowIndex, int endColIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetRangeFormatType(this.Workbook, sheetIndex, startRowIndex, startColumnIndex, endRowIndex, endColIndex, formatType);
}
catch (Exception ex)
{
new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Changes the column format type (eg: text, general, Date, number) for specified column address.
/// </summary>
/// <param name="sheetIndex">sheet index number</param>
/// <param name="columnAddress">column address. foreg: A:A , B:B</param>
/// <param name="formatType">format type.</param>
public void SetColumnFormatType(string columnAddress, int sheetIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetColumnFormatType(this.Workbook, sheetIndex, columnAddress, formatType);
}
catch (Exception ex)
{
new ApplicationException(ex.ToString());
}
}
public void SetCellFormatType(int sheetIndex, int rowIndex, int colIndex, NumberFormatType formatType)
{
try
{
ExcelHelper.SetCellFormatType(this.Workbook, sheetIndex, rowIndex, colIndex, formatType);
}
catch (Exception ex)
{
new ApplicationException(ex.ToString());
}
}
