public ColumnInfo(string header, int width, CellDataType type)
{
this.Header = header;
this.Width = width;
this.CellDataType = type;
AutoFilter = false;
}
private static CellDataType GetCellDataType(DataColumn column)
{
CellDataType dataType = CellDataType.String;
switch (Type.GetTypeCode(column.DataType))
{
case TypeCode.DateTime:
dataType = CellDataType.DateTime;
break;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Byte:
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.SByte:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
dataType = CellDataType.Number;
break;
case TypeCode.Boolean:
dataType = CellDataType.Boolean;
break;
}
return(dataType);
}
private void checkType(CellDataType expected)
{
if (Type != expected)
{
throw new InvalidOperationException("Cell data type is " + Type + " not " + expected);
}
}
/***********************************
* CONSTRUCTORS
************************************/
// internal for time being - until full styling is required.
internal Cell(Row row, Column column, object value, CellDataType cellDataType, int styleIndex)
: base(row.Worksheet, styleIndex)
{
// this constructor should be merged with the one below
Row = row;
Column = column;
CellDataType = cellDataType;
_value = value;
}
private Cell(X.Cell cell, X.SharedStringTable sst)
{
CellId = cell.CellId;
RawValue = cell.Value;
switch (cell.CellType)
{
case X.CellDataType.None:
break;
case X.CellDataType.Bool:
BoolValue = Convert.ToBoolean(RawValue);
DataType = CellDataType.Bool;
break;
case X.CellDataType.ISO8601Date:
DateTimeValue = DateTime.FromOADate(Convert.ToDouble(RawValue));
break;
case X.CellDataType.Error:
ErrorText = RawValue;
DataType = CellDataType.String;
break;
case X.CellDataType.FormulaString:
StringValue = RawValue;
DataType = CellDataType.Formula;
break;
case X.CellDataType.InlineString:
StringValue = RawValue;
DataType = CellDataType.String;
break;
case X.CellDataType.Number:
DataType = CellDataType.Number;
NumberValue = Convert.ToDouble(RawValue);
break;
case X.CellDataType.SharedString:
StringValue = sst.Instances[int.Parse(RawValue)].Value;
DataType = CellDataType.String;
break;
default:
throw new Exception("Invalid value for c.@t (how did this happen?)");
}
var idParts = ParseCellId(CellId);
Column = idParts.Item1;
Row = idParts.Item2;
}
public static Cell ReadNew(BinaryReader reader)
{
CellDataType type = EnumUtils.intToEnum <CellDataType>(reader.ReadInt32() + 1);
int columnIndex = reader.ReadInt32();
// We could do better with dynamic, but Unity can't
switch (type)
{
case CellDataType.String:
{
string value = reader.ReadZString();
return(new Cell(value, columnIndex));
}
case CellDataType.Integer:
{
readFixedSize(reader, 4);
int value = reader.ReadInt32();
return(new Cell(value, columnIndex));
}
case CellDataType.Byte:
{
readFixedSize(reader, 1);
byte value = reader.ReadByte();
return(new Cell(value, columnIndex));
}
case CellDataType.ForeignKey:
{
readFixedSize(reader, 8);
ForeignKey value = ForeignKey.ReadNew(reader);
return(new Cell(value, columnIndex));
}
case CellDataType.Buffer:
{
int length = reader.ReadInt32();
byte[] value = reader.ReadBytes(length);
return(new Cell(value, columnIndex));
}
default:
throw new InvalidDataException("Unknown cell data type");
}
}
private void testCellType(CellDataType expected, Cell cell)
{
Assert.AreEqual(expected, cell.Type);
if (expected != CellDataType.String)
{
Assert.That(() => cell.String, Throws.Exception);
}
if (expected != CellDataType.Integer)
{
Assert.That(() => cell.Integer, Throws.Exception);
}
if (expected != CellDataType.Byte)
{
Assert.That(() => cell.Byte, Throws.Exception);
}
if (expected != CellDataType.ForeignKey)
{
Assert.That(() => cell.ForeignKey, Throws.Exception);
}
if (expected != CellDataType.Buffer)
{
Assert.That(() => cell.Buffer, Throws.Exception);
}
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="cell">要读取数据的单元格</param>
public object ReadCell(ICell cell, CellDataType cellDataType)
{
if (cellDataType == CellDataType.Boolean)
{
return cell.BooleanCellValue;
}
else if (cellDataType == CellDataType.Date)
{
return cell.DateCellValue;
}
else if (cellDataType == CellDataType.DateTime)
{
return cell.DateCellValue;
}
else if (cellDataType == CellDataType.Double)
{
return cell.NumericCellValue;
}
else if (cellDataType == CellDataType.Formula)
{
return cell.CellFormula;
}
else if (cellDataType == CellDataType.Int)
{
return (int)cell.NumericCellValue;
}
else if (cellDataType == CellDataType.RichText)
{
return cell.RichStringCellValue;
}
else if (cellDataType == CellDataType.Text)
{
return cell.ToString();
}
else if (cellDataType == CellDataType.None)
{
return string.Empty;
}
else if (cellDataType == CellDataType.Null)
{
return null;
}
return null;
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="row">要读取数据的行实例</param>
/// <param name="columnNumber">要读取数据的列号</param>
public object ReadCell(IRow row, int columnNumber, CellDataType cellDataType)
{
var cell = row.GetCell(columnNumber);
if (cell != null)
{
return ReadCell(cell, cellDataType);
}
return null;
}
/// <summary>
/// 读取单元格数据
/// </summary>
/// <param name="sheet">要读取数据的sheet表实例</param>
/// <param name="rowNumber">要读取数据的行号</param>
/// <param name="columnNumber">要读取数据的列号</param>
public object ReadCell(ISheet sheet, int rowNumber, int columnNumber, CellDataType cellDataType)
{
var row = sheet.GetRow(rowNumber);
if (row != null)
{
return ReadCell(row, columnNumber, cellDataType);
}
return null;
}
/// <summary>
/// 写入数据到单元格
/// </summary>
/// <param name="cell">要写入数据的单元格实例</param>
/// <param name="value">要写入的数据</param>
/// <param name="dataType">要写入数据的类型</param>
public void WriteCell(ICell cell, object value, CellDataType dataType)
{
if (dataType == CellDataType.None || dataType == CellDataType.Null || value == null || value == DBNull.Value)
{
cell.SetCellType(CellType.Blank);
return;
}
if (value is DateTime)
{
cell.SetCellValue((DateTime)value);
}
else if (value is int)
{
cell.SetCellValue((int)value);
}
else if (value is double)
{
cell.SetCellValue((double)value);
}
else if (value is decimal)
{
cell.SetCellValue(Convert.ToDouble(value));
}
else
{
cell.SetCellValue(value.ToString());
}
if (dataType != CellDataType.None)
{
switch (dataType)
{
case CellDataType.Date:
cell.CellStyle = cellStyleDictionary["date"];
break;
case CellDataType.DateTime:
cell.CellStyle = cellStyleDictionary["datetime"];
break;
case CellDataType.Double:
cell.CellStyle = cellStyleDictionary["double"];
break;
default:
cell.CellStyle = cellStyleDictionary["text"];
break;
}
}
}
/// <summary>
/// 写入数据到单元格
/// </summary>
/// <param name="row">要写入数据的行实例</param>
/// <param name="columnNumber">要写入数据的列号</param>
/// <param name="value">要写入的数据</param>
/// <param name="dataType">要写入数据的类型</param>
public void WriteCell(IRow row, int columnNumber, object value, CellDataType dataType)
{
var cell = row.GetCell(columnNumber);
if (cell == null)
{
cell = row.CreateCell(columnNumber);
}
WriteCell(cell, value, dataType);
}
public WorksheetCell Add(string Value, string StyleName, CellDataType DataType)
{
WorksheetCell cell = new WorksheetCell();
cell.Data.Value = Value;
cell.StyleName = StyleName;
cell.Data.Type = DataType;
_Cells.Add(cell);
return cell;
}
private static object GetCellValueFromReader(CustomOpenXmlReader reader, CellDataType cellDataType, Row row)
{
object cellValue = null;
while (reader.ReadToEndElement<OpenXmlSpreadsheet.Cell>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.CellValue>())
{
string rawValue = reader.GetText();
if (rawValue != "")
{
switch (cellDataType)
{
case CellDataType.Boolean:
cellValue = (rawValue == "1" ? true : false);
break;
case CellDataType.SharedString:
int sharedStringIndex = int.Parse(rawValue);
cellValue = row.Worksheet.Workbook.SharedStrings[sharedStringIndex];
break;
case CellDataType.Number:
double doubleValue = 0;
if (double.TryParse(rawValue, out doubleValue))
cellValue = doubleValue;
break;
case CellDataType.String:
cellValue = rawValue.ToString();
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
}
return cellValue;
}
private static string GetAttributeValueFromCellDataType(CellDataType cellDataType)
{
switch (cellDataType)
{
case CellDataType.Number:
case CellDataType.Blank:
return "";
default:
return new OpenXml.EnumValue<OpenXmlSpreadsheet.CellValues>((OpenXmlSpreadsheet.CellValues)((int)cellDataType));
}
}
/// <summary>
/// 写入数据到单元格
/// </summary>
/// <param name="sheet">要写入数据的sheet表实例</param>
/// <param name="rowNumber">要写入数据的行号</param>
/// <param name="columnNumber">要写入数据的列号</param>
/// <param name="value">要写入的数据</param>
/// <param name="dataType">要写入数据的类型</param>
public void WriteCell(ISheet sheet, int rowNumber, int columnNumber, object value, CellDataType dataType)
{
var row = sheet.GetRow(rowNumber);
if (row == null)
{
row = sheet.CreateRow(rowNumber);
}
WriteCell(row, columnNumber, value, dataType);
}
/// <summary>
/// Constructeur.
/// </summary>
/// <param name="value">La valeur.</param>
/// <param name="dataType">Le type de la valeur.</param>
public CellContent(string value, CellDataType dataType)
{
this.Value = value;
this.DataType = dataType;
}
/// <summary>
/// Obtient l'index du style pour le type de données spécifié.
/// </summary>
/// <param name="dataType">le type de données.</param>
/// <param name="wrapText"><c>true</c> si le texte doit retourner à la ligne.</param>
/// <returns>
/// L'index du style.
/// </returns>
private uint GetCellStyleIndex(CellDataType dataType, bool wrapText)
{
var stylesheet = _package.WorkbookPart.WorkbookStylesPart.Stylesheet;
var numberingFormats = stylesheet.NumberingFormats;
if (numberingFormats == null)
{
numberingFormats = new NumberingFormats();
stylesheet.NumberingFormats = numberingFormats;
}
var cellFormats = stylesheet.CellFormats;
if (cellFormats == null)
{
cellFormats = new CellFormats();
stylesheet.Append(cellFormats);
}
string numberFormatCode = null;
uint numberFormatId = 0; // Les format ids spécifiés correspondent à ceux définis par défaut dans Excel. Voir la liste dans la section 18.8.30 des specs ECMA OPEN XML
int cellStyleId = -1;
switch (dataType)
{
case CellDataType.String:
case CellDataType.Number:
break;
case CellDataType.TimeSpan:
numberFormatId = 46; // [h]:mm:ss
break;
case CellDataType.Date:
numberFormatId = 14; // mm-dd-yy
break;
case CellDataType.Hyperlink:
cellStyleId = 42; // Non présent dans les specs ECMA, mais présents dans Office > 2007
break;
case CellDataType.Percentage:
numberFormatId = 10; // 0.00%
break;
default:
throw new ArgumentOutOfRangeException();
}
// Rechercher le number format
if (numberFormatCode != null)
{
var numberingFormat = numberingFormats.Elements <NumberingFormat>().FirstOrDefault(nf => nf.FormatCode == numberFormatCode);
if (numberingFormat == null)
{
// Créer le numbering format
numberingFormat = new NumberingFormat()
{
FormatCode = numberFormatCode,
};
if (numberingFormats.Elements <NumberingFormat>().Any())
{
numberingFormat.NumberFormatId = numberingFormats.Elements <NumberingFormat>().Select(nf => nf.NumberFormatId).Max() + 1;
}
else
{
numberingFormat.NumberFormatId = 100; // Commencer à 100 pour ne pas écraser les formats livrés par défaut, qui sont connus par excel mais non présents dans le fichier.
}
numberingFormats.Append(numberingFormat);
}
numberFormatId = numberingFormat.NumberFormatId;
}
if (cellStyleId != -1)
{
var cellFormat = cellFormats.ChildElements.OfType <CellFormat>().FirstOrDefault(cf => cf.FormatId == 42);
if (cellFormat != null)
{
return((uint)cellFormats.IndexOf(cellFormat));
}
else
{
return(1);
}
}
if (numberFormatId != 0)
{
CellFormat cellFormat;
// rechercher le Cell format
if (wrapText)
{
cellFormat = cellFormats.Elements <CellFormat>().FirstOrDefault(cf => cf.NumberFormatId == numberFormatId && cf.ApplyAlignment != null && cf.ApplyAlignment == true && cf.Alignment != null && cf.Alignment.WrapText);
}
else
{
cellFormat = cellFormats.Elements <CellFormat>().FirstOrDefault(cf => cf.NumberFormatId == numberFormatId);
}
if (cellFormat == null)
{
cellFormat = new CellFormat()
{
NumberFormatId = numberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = numberFormatId != 0,
};
if (wrapText)
{
cellFormat.ApplyAlignment = true;
cellFormat.Alignment = new Alignment()
{
WrapText = true
};
}
cellFormats.Append(cellFormat);
}
return((uint)cellFormats.IndexOf(cellFormat));
}
else if (wrapText)
{
// Rechercher :
//<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
// <x:alignment wrapText="1" />
//</x:xf>
var cellFormat = cellFormats.Elements <CellFormat>().FirstOrDefault(cf => cf.NumberFormatId == 0 && cf.ApplyAlignment != null && cf.ApplyAlignment == true && cf.Alignment != null && cf.Alignment.WrapText);
if (cellFormat == null)
{
cellFormat = new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = false,
ApplyAlignment = true,
Alignment = new Alignment()
{
WrapText = true
},
};
cellFormats.Append(cellFormat);
}
return((uint)cellFormats.IndexOf(cellFormat));
}
else
{
return(0);
}
}
