/// <summary>
/// Sets a cell value
/// </summary>
/// <param name="cell">The cell</param>
/// <param name="value">The value to be assigned to the cell</param>
public static void SetCellValue(ICell cell, object value)
{
if (value == null)
cell.SetCellValue(null as string);
else if (value is string ||
value is String)
cell.SetCellValue(value as string);
else if (value is bool ||
value is Boolean)
cell.SetCellValue((bool) value);
else if (value is DateTime) {
//It works
var wb = cell.Sheet.Workbook;
var cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = cell.Sheet.Workbook.GetCreationHelper().CreateDataFormat().GetFormat("dd/mm/yyyy" );
cell.CellStyle = cellStyle;
cell.SetCellValue((DateTime) value);
}
else
cell.SetCellValue(Convert.ToDouble(value));
}
Cell CreateCell(ICell cell)
{
string value = null;
switch (cell.CellType)
{
case CellType.STRING:
value = cell.StringCellValue;
break;
case CellType.NUMERIC:
value = cell.NumericCellValue.ToString();
break;
case CellType.FORMULA:
switch (cell.CachedFormulaResultType)
{
case CellType.STRING:
value = cell.StringCellValue;
break;
case CellType.NUMERIC:
//excel trigger is probably out-of-date
value = (cell.CellFormula == "TODAY()" ? DateTime.Today.ToOADate() : cell.NumericCellValue).ToString();
break;
}
break;
}
return new Cell(cell.RowIndex, cell.ColumnIndex, value);
}
public IEnumerable<ICell> ProcessTest(
[PexAssumeUnderTest]global::PathfindingAlgorithms.Algorithms.Astar.Astar target,
ICell[,] cells,
Coordinates from,
Coordinates to
)
{
PexAssume.IsNotNull(cells);
PexAssume.IsTrue(cells.GetLength(0)* cells.GetLength(1) > 0);
PexAssume.IsTrue(from.Inside(new Coordinates(cells.GetLength(0) - 1, cells.GetLength(1) - 1)));
PexAssume.IsTrue(to.Inside(new Coordinates(cells.GetLength(0) - 1, cells.GetLength(1) - 1)));
PexAssume.IsTrue(cells.GetLowerBound(0) == 0);
PexAssume.IsTrue(cells.GetLowerBound(1) == 0);
bool f = true;
for (int x = cells.GetLowerBound(0); x <= cells.GetUpperBound(0); x++)
{
for (int y = cells.GetLowerBound(1); y <= cells.GetUpperBound(1); y++)
{
PexAssume.IsNotNull(cells[x, y]);
PexAssume.IsNotNull(cells[x, y].Coordinates);
f &= cells[x, y].Coordinates.Equals(new Coordinates(x, y));
}
}
PexAssume.IsTrue(f);
IEnumerable<ICell> result = target.Process(cells, from, to);
return result;
// TODO: добавление проверочных утверждений в метод AstarTest.ProcessTest(Astar, ICell[,], Coordinates, Coordinates)
}
private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell)
{
var value = type.GetProperty(cell.Field).GetValue(row);
if (value == null)
{
return;
}
if (value is DateTime)
{
workCell.SetCellValue((DateTime)value);
}
else if (value is int)
{
workCell.SetCellValue((int)value);
}
else if (value is double)
{
workCell.SetCellValue((double)value);
}
else
{
workCell.SetCellValue(value.ToString());
}
if (!string.IsNullOrWhiteSpace(cell.Format))
{
var cellStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat(cell.Format);
workCell.CellStyle = cellStyle;
}
}
/// <summary>
/// Compare cell objects
/// </summary>
/// <param name="other"> </param>
/// <returns> </returns>
public bool Equals(ICell other)
{
if (other == null) return false;
if (other.Alive != Alive) return false;
if (!other.Position.Equals(Position)) return false;
return true;
}
static void SetValueAndFormat(IWorkbook workbook, ICell cell, double value, short formatId)
{
cell.SetCellValue(value);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = formatId;
cell.CellStyle = cellStyle;
}
private void SetMemberValue(string member, ICell cell, TextWriter log)
{
var info = GetType().GetProperty(member);
if (info == null)
{
log.WriteLine("Property {0} is not defined in {1}", member, GetType().Name);
return;
}
if(info.PropertyType != typeof(string))
throw new NotImplementedException("This function was only designed to work for string properties.");
string value = null;
switch (cell.CellType)
{
case CellType.Numeric:
value = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
break;
case CellType.String:
value = cell.StringCellValue;
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
default:
log.WriteLine("There is no suitable value for {0} in cell {1}{2}, sheet {3}",
info.Name, CellReference.ConvertNumToColString(cell.ColumnIndex), cell.RowIndex + 1,
cell.Sheet.SheetName);
break;
}
info.SetValue(this, value);
}
public bool CheckForExit(ICell[,] playField)
{
this.playField = playField;
Queue<ICell> cellsOrder = new Queue<ICell>();
ICell startCell = this.playField[this.playerPosition.Row, this.playerPosition.Column];
cellsOrder.Enqueue(startCell);
HashSet<ICell> visitedCells = new HashSet<ICell>();
bool pathExists = false;
while (cellsOrder.Count > 0)
{
ICell currentCell = cellsOrder.Dequeue();
visitedCells.Add(currentCell);
if (this.ExitFound(currentCell))
{
pathExists = true;
break;
}
this.Move(currentCell, Direction.Down, cellsOrder, visitedCells);
this.Move(currentCell, Direction.Up, cellsOrder, visitedCells);
this.Move(currentCell, Direction.Left, cellsOrder, visitedCells);
this.Move(currentCell, Direction.Right, cellsOrder, visitedCells);
}
return pathExists;
}
public Cell(ICell cell, Grid parent, Point p)
{
_cell = cell;
var values = new List<int>();
if (IsDefined)
{
var value = (int) (cell.Value);
Result = value.ToString(CultureInfo.InvariantCulture);
ResultColor = _cellColors[value - 1];
}
else
{
foreach (var value in _allNumericValues)
{
if (cell.MayHaveValue(value))
{
values.Add((int) value);
}
}
Values =
Enumerable.Range(1, Width)
.ToList()
.Select(
value =>
Tuple.Create(value, new RelayCommand(() => parent.ValueChoosen(value, p)),
values.Contains(value), _cellColors[value - 1]));
Result = "?";
}
}
/// <summary>
/// Perform a single point crossover.
/// </summary>
public static void Mutate(ICell cell)
{
if (cell == null) throw new ArgumentNullException("cell");
// Iterate the tags and the resources within the tags
for (int i = 0; i < cell.ActiveTagsInModel; i++)
{
// Potentially modify existing tag resources
Tag activeTag = cell.GetTagByIndex(i);
for (int j = 0; j < activeTag.Data.Count; j++)
{
if (ShouldMutateThisPoint())
{
activeTag.Data[j] = Resource.Random(true);
}
}
// Potentially add a resource
if (activeTag.Data.Count < Tag.MaxSize && ShouldMutateThisPoint())
{
int insertionIndex = RandomProvider.Next(0, activeTag.Data.Count);
activeTag.Data.Insert(insertionIndex, Resource.Random(true));
}
// Potentially remove a resource
if (activeTag.Data.Count > 2 && ShouldMutateThisPoint())
{
activeTag.Data.RemoveRandom();
}
}
}
public Direction Move(ICell cell)
{
/*
Add current cell as visited
Get possible directions
Get all neighbouring cells that haven't been visited
If found
randomly choose one
push it onto stack
add to visited
set as current position
return cell
If not that means dead end
pop next cell from stack
if no more cells that means we're at the start again and maze is not solvable
else
add to visited (should already be there but HashSet means it doesn't matter if it is added again)
set as current position
return cell
*/
var random = new Random(DateTime.Now.Millisecond);
visited.Add(cell);
stack.Push(cell);
var possibleDirections = GetPossibleDirections(cell);
if (possibleDirections.Any()) return possibleDirections[random.Next(possibleDirections.Count)];
if (stack.Count <= 0) return Direction.None; // We're back at the start and the maze is not solvable
// Backtrack
var previousCell = stack.Pop();
visited.Add(previousCell);
return GetDirection(cell, previousCell);
}
protected override double CalculateInterfernece(ICell[] position)
{
double totalInterference = 0;
foreach (ICellRelation cellRelation in InterferenceMatrix)
{
ICell cell = position[cellRelation.CellIndex[0]];
ICell interferingCell = position[cellRelation.CellIndex[1]];
double interference = 0;
for (int i = 0; i < interferingCell.Frequencies.Length; i++)
{
for (int j = 0; j < cell.Frequencies.Length; j++)
{
int frequencyA = channels[interferingCell.Frequencies[i].Value];
int frequencyB = channels[cell.Frequencies[j].Value];
double trxInf = 0;
if (SameFrequency(frequencyA, frequencyB))
{
trxInf = ZeroIfOusideInterferneceThreshold(cellRelation.DA[0]);
interference += trxInf;
}
else if (base.FrequenciesDifferByOne(frequencyA, frequencyB))
{
trxInf = ZeroIfOusideInterferneceThreshold(cellRelation.DA[1]);
interference += trxInf;
}
cell.FrequencyHandler.SetSingleTrxInterference(j, trxInf);
}
//cell.Interference += interference;
}
cell.Interference += interference;
totalInterference += interference;
}
return totalInterference;
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
private void SwapLookup(ICell cell)
{
#pragma warning disable 0420
//Ok to ignore CS0420 "a reference to a volatile field will not be treated as volatile" for interlocked calls http://msdn.microsoft.com/en-us/library/4bw5ewxy(VS.80).aspx
Interlocked.Exchange(ref _lookup_DoNotCallMeDirectly, cell);
#pragma warning restore 0420
}
public static void ThrowCommonError(int rowIndex, int colIndex, ICell cell)
{
string errorValue = string.Empty;
if (cell != null)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
errorValue = cell.StringCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
{
errorValue = cell.NumericCellValue.ToString("0.########");
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.BOOLEAN)
{
errorValue = cell.NumericCellValue.ToString();
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.BLANK)
{
errorValue = "Null";
}
else
{
errorValue = "Unknow value";
}
}
throw new BusinessException("Import.Read.CommonError", (rowIndex + 1).ToString(), (colIndex + 1).ToString(), errorValue);
}
public virtual bool Evaluate(ICell cell, ICellContext context)
{
bool evaluated = false;
Parallel.ForEach<IRule>(Rules, r => { evaluated |= r.Evaluate(cell, context); });
return evaluated;
}
public void LoadDataIntoCells(ICell[] cells)
{
while (TokenNode != null)
{
switch (TokenNode.Value.Tag)
{
case Tag.FORMAT_START:
{
FAPFileVerifier verifier = new FAPFileVerifier(TokenNode);
if (verifier.IsAssignmentFormat() == false)
{
throw new InvalidFAPFileException("The Format type of the given assignment file is not of an \"ASSIGNMENT\" type");
}
}
break;
case Tag.GENERATION_INFORMATION_START:
{
FAPFileVerifier verifier = new FAPFileVerifier(TokenNode);
if (verifier.ValidScenarioID(ScenarioID) == false)
{
throw new InvalidScenarioIDException("The given assignment file scenario id doesn't match the problem file scenario id");
}
}
break;
case Tag.CELLS_START: FillCells(cells);
break;
}
TokenNode = TokenNode.Next;
}
}
private void Confirm(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.Formula);
Confirm("PROPER(\"hi there\")", "Hi There");
Confirm("PROPER(\"what's up\")", "What'S Up");
Confirm("PROPER(\"I DON'T TH!NK SO!\")", "I Don'T Th!Nk So!");
Confirm("PROPER(\"dr\u00dcb\u00f6'\u00e4 \u00e9lo\u015f|\u00eb\u00e8 \")", "Dr\u00fcb\u00f6'\u00c4 \u00c9lo\u015f|\u00cb\u00e8 ");
Confirm("PROPER(\"hi123 the123re\")", "Hi123 The123Re");
Confirm("PROPER(\"-\")", "-");
Confirm("PROPER(\"!\u00a7$\")", "!\u00a7$");
Confirm("PROPER(\"/&%\")", "/&%");
// also test longer string
StringBuilder builder = new StringBuilder("A");
StringBuilder expected = new StringBuilder("A");
for (int i = 1; i < 254; i++)
{
builder.Append((char)(65 + (i % 26)));
expected.Append((char)(97 + (i % 26)));
}
Confirm("PROPER(\"" + builder.ToString() + "\")", expected.ToString());
}
/// <summary>
/// Splits the expression.
/// </summary>
/// <param name="cell">The cell.</param>
/// <param name="numbers">The numbers.</param>
/// <param name="operations">The operations.</param>
/// <exception cref="System.ArgumentNullException"></exception>
public static void SplitExpression(ICell cell, out int[] numbers, out char[] operations)
{
if (cell == null) throw new ArgumentNullException(nameof(cell));
var nums = new List<int>();
var opers = new List<char>();
int num = 0;
var expressionValue = new ExpressionValue(cell.Value.ToCharArray());
for (int i = 0; i < expressionValue.Chars.Length; i++)
{
expressionValue.Index = i;
if (CellConst.OperationSymbols.Contains(expressionValue.Chars[i]))
{
Operation(nums, opers, ref num, expressionValue);
}
else
{
Number(expressionValue);
}
}
num = Convert.ToInt32(expressionValue.Value);
nums.Add(num);
numbers = nums.ToArray();
operations = opers.ToArray();
}
private static Coordinates? TestNode(ICell[,] grid, int x, int y)
{
int xsize = grid.GetLength(0), ysize = grid.GetLength(1);
if (x >= 0 && x < xsize && y >= 0 && y < ysize && grid[x, y].Weight >= 0 && grid[x, y].Weight < 1)
return grid[x, y].Coordinates;
return null;
}
/// <summary>
/// Constructor with 2 parameters
/// </summary>
/// <param name="name">String that represents the name of the player</param>
/// <param name="cell">Object of type ICell that represents the current position of the player</param>
public Player(string name, ICell cell)
{
this.MovesCount = 0;
this.Name = name;
this.CurentCell = cell;
this.StartPosition = cell.Position;
}
public RuInterferenceVictim(ICell cell)
{
CellId = cell.CellId;
SectorId = cell.SectorId;
MeasuredTimes = 0;
InterferenceTimes = 0;
}
private static void CheckExpressionParts(ICell cell)
{
string firstSymbol = cell.Value.Substring(0, 1);
if (firstSymbol.Equals("*") || firstSymbol.Equals("/"))
{
cell.SetError("First symbol can not be multiple or divide");
}
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.FORMULA);
Evaluator = new HSSFFormulaEvaluator(wb);
}
public bool Evaluate(ICell cell, ICellContext context)
{
bool condition = Condition(cell, context);
if (condition)
context.FutureHealth = Result(cell.Health);
return condition;
}
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
String expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.STRING);
Assert.AreEqual(expectedResult, result.StringValue);
}
public void MatrixMementoConstructProperMatrix(int rows, int cols)
{
ICell[,] field = new ICell[rows, cols];
MatrixMemento memento = new MatrixMemento(field);
Assert.AreEqual(field.GetLength(0), memento.Field.GetLength(0), "Memento field expected rows are not equal to actual rows");
Assert.AreEqual(field.GetLength(1), memento.Field.GetLength(1), "Memento field expected cols are not equal to actual cols");
}
private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedErrorCode)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.ERROR);
Assert.AreEqual(expectedErrorCode, result.ErrorValue);
}
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.NUMERIC);
Assert.AreEqual(expectedResult, result.NumberValue, 0.0);
}
public int CountViolations(ICell[] cells)
{
int violations = 0;
foreach (ICell item in cells)
{
violations += CountViolations(item);
}
return violations;
}
public static List <T> ReadExcelNoIndex <T>(string filePath, string sheetName)
where T : new()
{
List <T> result = null;
if (!File.Exists(filePath))
{
throw new FileNotFoundException(filePath + " not exists.");
}
List <ExcelColumnMapping> mappings = new List <ExcelColumnMapping>();
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
var attr = property.GetCustomAttribute <ExcelColumnNameAttribute>();
if (attr != null)
{
mappings.Add(new ExcelColumnMapping()
{
Property = property, ColumnIndex = mappings.Count, ColumnName = (attr as ExcelColumnNameAttribute).ColumnName
});
}
}
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(stream);
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
throw new FormatException("sheet not exists");
}
result = new List <T>();
IRow row = null;
ICell cell = null;
T data = default(T);
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
data = new T();
try
{
for (int j = 0; j < mappings.Count; j++)
{
ExcelColumnMapping map = mappings[j];
cell = row.GetCell(map.ColumnIndex);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
if (map.Property.PropertyType == typeof(double))
{
map.Property.SetValue(data, cell.NumericCellValue);
}
else if (cell.DateCellValue != null)
{
map.Property.SetValue(data, cell.DateCellValue);
}
}
else
{
if (!string.IsNullOrEmpty(cell.StringCellValue))
{
cell.SetCellType(CellType.String);
if (map.ColumnName == "联系电话" || map.ColumnName == "订单编号" ||
map.ColumnName == "联系手机" || map.ColumnName == "手机")
{
map.Property.SetValue(data, cell.StringCellValue.Replace("\"", "").Replace("=", "").Replace("'", ""));
}
else
{
map.Property.SetValue(data, cell.StringCellValue);
}
}
}
}
}
result.Add(data);
}
catch (Exception ex)
{
throw new Exception($"第{i}行,{ex.Message}");
}
}
}
return(result);
}
/// <summary>
/// 导入数据
/// </summary>
/// <returns></returns>
public SavedResult <Int64> SaveImportData(string fileExtension, string filePath, string clear, string ProductBZ, long ProductPhid)
{
IList <QTProductUserModel> qTProductUserList = new List <QTProductUserModel>();
if ("1".Equals(clear))
{
try
{
Dictionary <string, object> dicwhere = new Dictionary <string, object>();
new CreateCriteria(dicwhere).Add(ORMRestrictions <string> .Eq("ProductBZ", ProductBZ));
base.Delete(dicwhere);
}
catch (Exception e)
{
return(null);
}
}
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
if (".xls".Equals(fileExtension))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
QTProductUserModel qTProductUser = new QTProductUserModel();
IRow row = sheet.GetRow(i);
ICell cell1 = row.GetCell(0);
ICell cell2 = row.GetCell(1);
ICell cell3 = row.GetCell(2);
cell1.SetCellType(CellType.String);
cell2.SetCellType(CellType.String);
cell3.SetCellType(CellType.String);
string ProductUserCode = cell1.StringCellValue;
string ProductUserPwd = cell2.StringCellValue;
string ProductUserName = cell3.StringCellValue;
qTProductUser.ProductUserCode = ProductUserCode;
qTProductUser.ProductUserPwd = ProductUserPwd;
qTProductUser.ProductUserName = ProductUserName;
qTProductUser.ProductBZ = ProductBZ;
qTProductUser.ProductPhid = ProductPhid;
qTProductUser.PersistentState = PersistentState.Added;
qTProductUserList.Add(qTProductUser);
}
}
else if (".xlsx".Equals(fileExtension))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
QTProductUserModel qTProductUser = new QTProductUserModel();
IRow row = sheet.GetRow(i);
ICell cell1 = row.GetCell(0);
ICell cell2 = row.GetCell(1);
ICell cell3 = row.GetCell(2);
cell1.SetCellType(CellType.String);
cell2.SetCellType(CellType.String);
cell3.SetCellType(CellType.String);
string ProductUserCode = cell1.StringCellValue;
string ProductUserPwd = cell2.StringCellValue;
string ProductUserName = cell3.StringCellValue;
qTProductUser.ProductUserCode = ProductUserCode;
qTProductUser.ProductUserPwd = ProductUserPwd;
qTProductUser.ProductUserName = ProductUserName;
qTProductUser.ProductBZ = ProductBZ;
qTProductUser.ProductPhid = ProductPhid;
qTProductUser.PersistentState = PersistentState.Added;
qTProductUserList.Add(qTProductUser);
}
}
}
catch (Exception e)
{
return(null);
}
}
return(base.Save <Int64>(qTProductUserList, ""));
}
/// <summary>获取单元格的值
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static object GetCellValue(ICell cell)
{
switch (cell.CellType)
{
case CellType.String:
string str = cell.StringCellValue;
if (str != null && str.Length > 0)
{
return(str.ToString());
}
else
{
return(null);
}
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return(DateTime.FromOADate(cell.NumericCellValue).ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
else
{
return(Convert.ToDouble(cell.NumericCellValue));
}
case CellType.Boolean:
return(Convert.ToString(cell.BooleanCellValue));
case CellType.Error:
return(ErrorEval.GetText(cell.ErrorCellValue));
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = cell.StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
return(strFORMULA.ToString());
}
else
{
return(null);
}
case CellType.Numeric:
return(Convert.ToString(cell.NumericCellValue));
case CellType.Boolean:
return(Convert.ToString(cell.BooleanCellValue));
case CellType.Error:
return(ErrorEval.GetText(cell.ErrorCellValue));
default:
return("");
}
default:
return("");
}
}
//DB导出Excel
private void button1_Click(object sender, EventArgs e)
{
//1.通过Ado.net读取数据
string sql = "SELECT * FROM T_Seats";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
IWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("T_Seats");
#region 创建第一行，设置列名
//--------------------------------------------------
//创建第一行，第一行表示列名
IRow rowHead = sheet.CreateRow(0);
//循环查询出的每一列
for (int col = 0; col < reader.FieldCount; col++)
{
rowHead.CreateCell(col).SetCellValue(reader.GetName(col));
}
//--------------------------------------------------
#endregion
int rindex = 1;
//下面是创建数据行
while (reader.Read())
{
//CC_AutoId, CC_LoginId, CC_LoginPassword, CC_UserName, CC_ErrorTimes, CC_LockDateTime, CC_TestInt
IRow currentRow = sheet.CreateRow(rindex);
rindex++;
int autoId = reader.GetInt32(0);
string loginId = reader.GetString(1);
string password = reader.GetString(2);
string username = reader.GetString(3);
int errorTimes = reader.GetInt32(4);
DateTime?lockDate = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
int? testInt = reader.IsDBNull(6) ? null : (int?)reader.GetInt32
(6);
currentRow.CreateCell(0).SetCellValue(autoId);
currentRow.CreateCell(1).SetCellValue(loginId);
currentRow.CreateCell(2).SetCellValue(password);
currentRow.CreateCell(3).SetCellValue(username);
currentRow.CreateCell(4).SetCellValue(errorTimes);
if (lockDate == null)
{
//如果是null值，那么就像excel写入一个单元格，这个单元格的类型就是Blank
currentRow.CreateCell(5).SetCellType(CellType.Blank);
}
else
{
//创建一个单元格
ICell cellLockDate = currentRow.CreateCell(5);
//创建一个单元格样式
ICellStyle cellStyle = wk.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
//设置当前单元格应用cellStyle样式
cellLockDate.CellStyle = cellStyle;
cellLockDate.SetCellValue((DateTime)lockDate);
}
if (testInt == null)
{
currentRow.CreateCell(6).SetCellType(CellType.Blank);
}
else
{
currentRow.CreateCell(6).SetCellValue((int)testInt);
}
}
//写入
using (FileStream fsWrite = File.OpenWrite("tseats.xls"))
{
wk.Write(fsWrite);
}
//label1.Text = "写入成功！" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
}
else
{
//label1.Text = "没有查询到任何数据";
}
}
//2.写入excel
}
public ICellRange <ICell> RemoveArrayFormula(ICell cell)
{
return(_sh.RemoveArrayFormula(cell));
}
public static bool has(this ICell cell, string field)
{
return(cell.ContainsField(field));
}
public static Action continue_if(this ICell cell, bool pred)
{
return(pred ? Action.Continue : ~(Action)0);
}
private static void ConfirmCannotReadString(ICell cell)
{
AssertProhibitedValueAccess(cell, CellType.String);
}
public List <ArrayList> getExcel([FromBody] snWfs namePrm)
{
FileStream fs = new FileStream(namePrm.fs, FileMode.Open);
IWorkbook workbook = WorkbookFactory.Create(fs);
List <ArrayList> res = new List <ArrayList>();
List <ArrayList> err = new List <ArrayList>();
ArrayList errList = new ArrayList();
errList.Add("表格存在非法字段");
ArrayList eptSheet = new ArrayList();
eptSheet.Add("表格无数据");
ISheet sheet = null;
for (var i = 0; i < workbook.NumberOfSheets; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet.SheetName == namePrm.sn)
{
break;
}
}
if (sheet.LastRowNum == 0)
{
err.Add(eptSheet);
return(err);
}
//获取标题行列数
int index = sheet.GetRow(0).Cells.Count;
IRow row = null;
ICell cell = null;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = (dynamic)sheet.GetRow(i);
ArrayList resSub = new ArrayList();
for (int j = 0; j < index; j++)
{
cell = (dynamic)row.GetCell(j);
if (cell == null)
{
resSub.Add("空");
}
else
{
if (j == 4 || j == 5 || j == 6)
{
if (cell.CellType == CellType.Numeric)
{
resSub.Add(cell.DateCellValue);
}
else
{
errList.Add(i);
errList.Add(j);
err.Add(errList);
return(err);
}
}
else if (j == 3)
{
if (cell.CellType == CellType.Numeric)
{
resSub.Add(cell.ToString().Trim());
}
else
{
errList.Add(i);
errList.Add(j);
err.Add(errList);
return(err);
}
}
else
{
if (j == 7)
{
if (cell.ToString().Trim() != "EGR阀线" && cell.ToString().Trim() != "Cooler/Module线" && cell.ToString().Trim() != "电子线")
{
errList.Add(i);
errList.Add(j);
err.Add(errList);
return(err);
}
}
else if (j == 8)
{
if (cell.ToString().Trim() != "创建")
{
errList.Add(i);
errList.Add(j);
err.Add(errList);
return(err);
}
}
else if (j == 9)
{
if (cell.ToString().Trim() != "受控订单" && cell.ToString().Trim() != "试制订单")
{
errList.Add(i);
errList.Add(j);
err.Add(errList);
return(err);
}
}
resSub.Add(cell.ToString().Trim());
}
}
}
res.Add(resSub);
}
fs.Close();
// //处理完成后，删除上传的文件
// if (System.IO.File.Exists(namePrm.fs))
// {
// System.IO.File.Delete(namePrm.fs);
// }
return(res);
}
/// <summary>
/// DeSerialise the date held in the sheet into a COBieWorkbook
/// </summary>
/// <returns>COBieWorkbook with date imported from XLS file</returns>
public COBieWorkbook Deserialise()
{
try
{
GetXLSFileData(); //Read XLS file into the HSSFWorkbook object
foreach (string sheetname in SheetNames)
{
ISheet excelSheet = XlsWorkbook.GetSheet(sheetname); //get sheet name in XLS file
if (excelSheet != null)
{
ICOBieSheet <COBieRow> thisSheet = GetSheetType(sheetname);
int COBieColumnCount = thisSheet.Columns.Count;
//no checking on Sheet column count to XLS sheet column count, just extract up to the column number in the COBieSheet/Row
int rownumber = 0;
int columnCount = 0;
foreach (IRow row in excelSheet)
{
if (rownumber == 0) //this will be the headers so get how many we have
{
foreach (ICell cell in row)
{
columnCount++;
}
}
else
{
bool addRow = false;
//check we have some data on the row
for (int i = 0; i < columnCount; i++)
{
ICell cell = row.GetCell(i);
if ((cell != null) && (cell.CellType != CellType.Blank))
{
addRow = true;
break;
}
}
//add a none blank row
if (addRow)
{
COBieRow sheetRow = thisSheet.AddNewRow(); //add a new empty COBie row to the sheet
for (int i = 0; i < thisSheet.Columns.Count(); i++) //changed from columnCount to supported column count of the sheet
{
string cellValue = ""; //default value
ICell cell = row.GetCell(i);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Numeric:
if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODate)
{
cellValue = cell.DateCellValue.ToString(Constants.DATE_FORMAT);
}
else if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODateTime)
{
DateTime date = DateTime.Now;
try
{
date = cell.DateCellValue;
}
catch
{
// If we can't read a valid date, just use the current date.
date = DateTime.Now;
}
cellValue = date.ToString(Constants.DATETIME_FORMAT);
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
cellValue = cell.ErrorCellValue.ToString();
break;
case CellType.Blank:
case CellType.Formula:
case CellType.Unknown:
cellValue = cell.StringCellValue;
break;
default:
break;
}
}
if (i < COBieColumnCount) //check we are in the column range of the COBieRow and add value
{
COBieColumn cobieColumn = thisSheet.Columns.Where(idxcol => idxcol.Key == i).Select(idxcol => idxcol.Value).FirstOrDefault();
sheetRow[i] = new COBieCell(cellValue, cobieColumn);
}
}
}
}
rownumber++;
}
WorkBook.Add(thisSheet);
}
}
}
catch (FileNotFoundException)
{
//TODO: Report this
throw;
}
catch (Exception)
{
throw;
}
WorkBook.CreateIndices();
return(WorkBook);
}
public IEnumerator Cells_TryCheck(int equalsCellsCount, bool isVertical)
{
#region Create Managers And Board
IMasterManager masterManager;
ICellRegistrator cellRegistrator;
IBoard board = ObjectsCreator.CreateBoard(equalsCellsCount + 3, equalsCellsCount + 3, out masterManager, out cellRegistrator);
IUpdateManager updateManager = masterManager.UpdateManager;
IGameplayLogicManager gameplayLogicManager = ObjectsCreator.CreateGameplayLogicManager();
INotifier gameplayNotifier = masterManager.GameplayNotifier;
ISpawnManager spawnManager = masterManager.SpawnManager;
IInputManager inputManager = new InputManager(gameplayNotifier);
ICheckManager checkManager = new CheckManager();
#endregion
#region Create Cells
int startX = 2;
int startY = 2;
ICell swipedCell = null;
for (int i = 0; i < equalsCellsCount; i++)
{
int x = startX;
int y = startY;
if (isVertical)
{
if (i == Mathf.Floor(equalsCellsCount / 2))
{
x = startX + 1;
}
else
{
x = startX;
}
y = startY + i;
}
else
{
if (i == Mathf.Floor(equalsCellsCount / 2))
{
y = startY + 1;
}
else
{
y = startY;
}
x = startX + i;
}
ICell cell = new NormalCell(x, y);
cell.CurrentGameObject = spawnManager.SpawnPrefab(GameElementTypesEnum.RedCircle, new Vector3(x, y, 0));
cellRegistrator.RegistrateNormalCell(cell as NormalCell);
board.Cells[cell.TargetX, cell.TargetY] = cell;
if (i == Mathf.Floor(equalsCellsCount / 2))
{
swipedCell = board.Cells[cell.TargetX, cell.TargetY];
}
}
#endregion
yield return(new WaitForSeconds(1f));
#region SetUp Board And Managers
board.Initial();
checkManager.Board = board;
gameplayLogicManager.Board = board;
gameplayLogicManager.CheckManager = checkManager;
gameplayLogicManager.SpawnManager = spawnManager;
gameplayLogicManager.Notifier = masterManager.UINotifier;
inputManager.AddSubscriber(gameplayLogicManager);
updateManager.AddUpdatable(inputManager as IUpdatable);
updateManager.IsUpdate = true;
#endregion
yield return(new WaitForSeconds(1f));
#region Create And SetUp MacroCommand to use swap cells
ICommand[] commands;
if (isVertical)
{
commands = new ICommand[]
{
new SwipeLeftCommand(swipedCell),
new SwipeRightCommand(board.Cells[swipedCell.TargetX - 1, swipedCell.TargetY]),
};
}
else
{
commands = new ICommand[]
{
new SwipeDownCommand(swipedCell),
new SwipeUpCommand(board.Cells[swipedCell.TargetX, swipedCell.TargetY - 1]),
};
}
ICommand macroCommand = new MacroCommand(commands);
gameplayLogicManager.MacroCommand = macroCommand;
#endregion
#region Try Swap Cells and get Check
gameplayLogicManager.MacroCommand.Execute();
yield return(new WaitForSeconds(1f));
gameplayLogicManager.TryCheckSwipedCells(swipedCell);
#endregion
#region Remove From Scene
yield return(new WaitForSeconds(0.2f));
updateManager.IsUpdate = false;
foreach (var boadrdCell in board.Cells)
{
updateManager.RemoveUpdatable(boadrdCell as IUpdatable);
}
foreach (var cell in board.Cells)
{
GameObject.Destroy(cell.CurrentGameObject);
}
#endregion
}
public void Stackoverflow26437323()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
IRow r2 = s.CreateRow(1);
// A1 is a number
r1.CreateCell(0).SetCellValue(1.1);
// B1 is a string, with the wanted text in it
r1.CreateCell(1).SetCellValue("This is text with AM in it");
// C1 is a string, with different text
r1.CreateCell(2).SetCellValue("This some other text");
// D1 is a blank cell
r1.CreateCell(3, CellType.Blank);
// E1 is null
// A2 will hold our test formulas
ICell cf = r2.CreateCell(0, CellType.Formula);
// First up, check that TRUE and ISLOGICAL both behave
cf.CellFormula = (/*setter*/ "TRUE()");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISLOGICAL(TRUE())");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISLOGICAL(4)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
// Now, check ISNUMBER / ISTEXT / ISNONTEXT
cf.CellFormula = (/*setter*/ "ISNUMBER(A1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNUMBER(B1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNUMBER(C1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNUMBER(D1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNUMBER(E1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISTEXT(A1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISTEXT(B1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISTEXT(C1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISTEXT(D1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISTEXT(E1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNONTEXT(A1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNONTEXT(B1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNONTEXT(C1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNONTEXT(D1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.CellFormula = (/*setter*/ "ISNONTEXT(E1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue); // Blank and Null the same
// Next up, SEARCH on its own
cf.SetCellFormula("SEARCH(\"am\", A1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(ErrorConstants.ERROR_VALUE, cf.ErrorCellValue);
cf.SetCellFormula("SEARCH(\"am\", B1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(19, (int)cf.NumericCellValue);
cf.SetCellFormula("SEARCH(\"am\", C1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(ErrorConstants.ERROR_VALUE, cf.ErrorCellValue);
cf.SetCellFormula("SEARCH(\"am\", D1)");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(ErrorConstants.ERROR_VALUE, cf.ErrorCellValue);
// Finally, bring it all together
cf.SetCellFormula("ISNUMBER(SEARCH(\"am\", A1))");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.SetCellFormula("ISNUMBER(SEARCH(\"am\", B1))");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(true, cf.BooleanCellValue);
cf.SetCellFormula("ISNUMBER(SEARCH(\"am\", C1))");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.SetCellFormula("ISNUMBER(SEARCH(\"am\", D1))");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
cf.SetCellFormula("ISNUMBER(SEARCH(\"am\", E1))");
cf = EvaluateCell(wb, cf);
Assert.AreEqual(false, cf.BooleanCellValue);
}
public static List <T> ReadExcel <T>(string filePath, string sheetName)
where T : new()
{
List <T> result = null;
if (!File.Exists(filePath))
{
throw new FileNotFoundException(filePath + " not exists.");
}
List <ExcelColumnMapping> mappings = new List <ExcelColumnMapping>();
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
var attr = property.GetCustomAttribute <ExcelColumnNameAttribute>();
var indexAttr = property.GetCustomAttribute <ExcelColumnIndexAttribute>();
if (attr != null)
{
mappings.Add(new ExcelColumnMapping()
{
Property = property, ColumnIndex = (indexAttr as ExcelColumnIndexAttribute).ColumnIndex, ColumnName = (attr as ExcelColumnNameAttribute).ColumnName
});
}
}
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(stream);
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
throw new FormatException("sheet not exists");
}
result = new List <T>();
IRow row = null;
ICell cell = null;
T data = default(T);
//读取列头
var excelColumnHeads = sheet.GetRow(0);
foreach (var item in excelColumnHeads.Cells)
{
cell = item;
if (cell != null)
{
cell.SetCellType(CellType.String);
var value = cell.StringCellValue;
var mapping = mappings.FirstOrDefault(m => m.ColumnName == value.Trim());
if (mapping != null)
{
mapping.ColumnIndex = excelColumnHeads.Cells.IndexOf(item);
}
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
data = new T();
try
{
for (int j = 0; j < mappings.Count; j++)
{
ExcelColumnMapping map = mappings[j];
cell = row.GetCell(map.ColumnIndex);
if (cell != null)
{
cell.SetCellType(CellType.String);
var value = cell.StringCellValue;
if (map.ColumnName == "联系电话" || map.ColumnName == "订单编号" ||
map.ColumnName == "联系手机" || map.ColumnName == "手机")
{
value = value.Replace("\"", "").Replace("=", "").Replace("'", "");
}
map.Property.SetValue(data, value);
}
}
result.Add(data);
}
catch (Exception ex)
{
throw ex;
}
}
}
return(result);
}
public static unsafe long get_id_in_path(this ICell cell, int index)
{
return(m_path_ptr[index]);
}
internal void Insert(string date, int pm25, int pm10, int tsp, double noise, double velocity, int vane, double temperature, double humidity, double barometric)
{
Current++;
IRow row = currentSheet.CreateRow(Current);
ICell cell = row.CreateCell(0);
cell.SetCellValue(date);
cell.CellStyle = TimeDataStyle();
cell = row.CreateCell(1);
if (pm25 == -1)
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(pm25);
}
cell.CellStyle = TextDataStyle();
cell = row.CreateCell(2);
if (pm10 == -1)
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(pm10);
}
cell.CellStyle = TextDataStyle();
cell = row.CreateCell(3);
if (tsp == -1)
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(tsp);
}
cell.CellStyle = TextDataStyle();
cell = row.CreateCell(4);
if (Double.IsNaN(noise))
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(noise);
}
cell.CellStyle = DigitDataStyle();
cell = row.CreateCell(5);
if (Double.IsNaN(velocity))
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(velocity);
}
cell.CellStyle = DigitDataStyle();
cell = row.CreateCell(6);
if (vane > 15 || vane < 0)
{
cell.SetCellValue("异常:" + vane);
}
else
{
cell.SetCellValue(vanes[vane]);
}
cell.CellStyle = TextDataStyle();
cell = row.CreateCell(7);
if (Double.IsNaN(temperature))
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(temperature);
}
cell.CellStyle = DigitDataStyle();
cell = row.CreateCell(8);
if (Double.IsNaN(humidity))
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(humidity);
}
cell.CellStyle = DigitDataStyle();
cell = row.CreateCell(9);
if (Double.IsNaN(barometric))
{
cell.SetCellValue("--");
}
else
{
cell.SetCellValue(barometric);
}
cell.CellStyle = DigitDataStyle();
}
public static int get_path_length(this ICell cell)
{
return(m_path_len);
}
//把excel中的内容导入到表
private void button2_Click(object sender, EventArgs e)
{
//读取Excel
using (FileStream fsRead = File.OpenRead("tseats.xls"))
{
//创建 工作簿
IWorkbook wk = new HSSFWorkbook(fsRead);
//创建工作表
ISheet sheet = wk.GetSheetAt(0);
string insert_sql = "INSERT INTO NewTSeats VALUES(@uid,@pwd,@name,@errorTimes,@lockDate,@testInt)";
//遍历读取所有
for (int i = 1; i <= sheet.LastRowNum; i++)
{
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@uid", SqlDbType.NVarChar, 50),
new SqlParameter("@pwd", SqlDbType.VarChar, 50),
new SqlParameter("@name", SqlDbType.NVarChar, 50),
new SqlParameter("@errorTimes", SqlDbType.Int),
new SqlParameter("@lockDate", SqlDbType.DateTime),
new SqlParameter("@testInt", SqlDbType.Int)
};
//获取每一行
IRow currentRow = sheet.GetRow(i);
if (currentRow != null)//表示有该行对象
{
//遍历读取每一个单元格
for (int c = 1; c < currentRow.LastCellNum; c++)
{
ICell currentCell = currentRow.GetCell(c);
//判断单元格是否为空
if (currentCell == null || currentCell.CellType == CellType.Blank)
{
//表示空值，要向数据库中插入DBNull.Value
pms[c - 1].Value = DBNull.Value;
}
else
{
if (c == 5)
{
//pms[c - 1].Value = DateTime.Parse(currentCell.ToString());
//如果当前的列是一个日期类型，那么直接把该值作为一个number类型来读取，读取到后，通过DateTime.FromOADate()来转换为DateTime类型。
pms[c - 1].Value = DateTime.FromOADate(currentCell.NumericCellValue);
}
else
{
pms[c - 1].Value = currentCell.ToString();
}
}
}
SqlHelper.ExecuteNonQuery(insert_sql, CommandType.Text, pms);
}
}
}
//将数据插入到Db中
//label1.Text = "导入成功！" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
}
private static void SetSubTitleStyle(ICell cell)
{
cell.SetICellStyle(12, false, false);
}
private static void SetMainTitleStyle(ICell cell)
{
cell.SetICellStyle(15, false, false, true);
}
public static void Export(string fileName)
{
string path = @"D:\FCOMWCF\Final\File\WorkOrder.xls";
//string fileName = @"D:\FCOMWCF\Final\WorkOrder_test.xls";
HSSFWorkbook wk = null;
//FileStream fs = File.Open(path, FileAccess.ReadWrite, FileMode.Open, FileShare.ReadWrite);
using (FileStream fs = File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
wk = new HSSFWorkbook(fs);
fs.Close();
}
ISheet sheet = wk.GetSheet("sheet1");
int startIndex = 5;
int insertCounts = 2;
//sheet.ShiftRows(startIndex, sheet.LastRowNum, 2, true, false);
if (insertCounts < 1)
{
sheet.GetRow(5).GetCell(1).SetCellValue("北京");
sheet.GetRow(5).GetCell(2).SetCellValue(DateTime.Now.ToShortDateString());
sheet.GetRow(5).GetCell(3).SetCellValue("12,23,45");
sheet.GetRow(5).GetCell(4).SetCellValue("X");
sheet.GetRow(5).GetCell(5).SetCellValue(DateTime.Now.ToShortDateString());
}
else
{
IRow sourceRow = sheet.GetRow(startIndex - 1);
int sourceCellCount = sourceRow.Cells.Count;
//批量移动
sheet.ShiftRows(startIndex, sheet.LastRowNum, insertCounts, true, false);
int startMergeCell = -1;//记录每行合并单元格起始位置
for (int i = startIndex; i < startIndex + insertCounts; i++)
{
IRow targetRow = null;
ICell sourceCell = null;
ICell targetCell = null;
targetRow = sheet.CreateRow(i);
targetRow.Height = sourceRow.Height;//复制行高
int s = sourceRow.FirstCellNum;
for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
{
sourceCell = sourceRow.GetCell(m);
if (sourceCell == null)
{
continue;
}
targetCell = targetRow.CreateCell(m);
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
//复制模板行的单元格合并格式
if (sourceCell.IsMergedCell)
{
if (startMergeCell <= 0)
{
startMergeCell = m;
}
else if (startMergeCell > 0 && sourceCellCount == m + 1)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, startMergeCell, m));
}
}
else
{
if (startMergeCell >= 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, startMergeCell, m - 1));
}
}
}
}
IRow firstTargetRow = sheet.GetRow(startIndex);
ICell firstSourceCell = null;
ICell firstTargetCell = null;
for (int i = sheet.GetRow(startIndex).FirstCellNum; i < sheet.GetRow(startIndex).LastCellNum; i++)
{
firstSourceCell = sheet.GetRow(startIndex).GetCell(i);
if (firstSourceCell == null)
{
continue;
}
firstTargetCell = firstTargetRow.CreateCell(i);
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
firstTargetCell.SetCellValue("sdsdasd");
}
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 7 + 2, 0, 0));
#region
//第一行
var style = sheet.GetRow(1).GetCell(5).CellStyle;
sheet.GetRow(1).GetCell(5).SetCellValue("我的excel读取写入");
sheet.GetRow(1).GetCell(5).CellStyle = style;
style = sheet.GetRow(1).GetCell(2).CellStyle;
sheet.GetRow(1).GetCell(2).SetCellValue(string.Join(",", "6/23 - 6/29"));
sheet.GetRow(1).GetCell(5).CellStyle = style;
style = sheet.GetRow(2).GetCell(2).CellStyle;
sheet.GetRow(2).GetCell(2).SetCellValue("zy");
sheet.GetRow(2).GetCell(2).CellStyle = style;
DateTime dt;
var createOn = "";
if (DateTime.TryParse("2018-07-08", out dt))
{
createOn = dt.ToString();
}
style = sheet.GetRow(2).GetCell(5).CellStyle;
sheet.GetRow(2).GetCell(5).SetCellValue(createOn);
sheet.GetRow(2).GetCell(5).CellStyle = style;
sheet.GetRow(3).GetCell(2).SetCellValue("zy");
sheet.GetRow(3).GetCell(2).CellStyle = style;
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
byte[] bytes;
wk.Write(ms);
ms.Flush();
bytes = ms.ToArray();
ms.Write(bytes, 0, bytes.Length);
File.WriteAllBytes(fileName + "\\WorkOrder_test.xls", bytes);
}
for (int i = 0; i < 10; i++)
{
Thread thread = new Thread(fucs);
thread.Name = "test" + i;
}
}
/// <summary>
/// Adds a new cell to the key-value store if the cell Id does not exist, or updates an existing cell in the key-value store if the cell Id already exists.
/// Note that the generic cell will be saved as a strongly typed cell. It can then be loaded into either a strongly-typed cell or a generic cell.
/// The <paramref name="cellId"/> overrides the cell id in <paramref name="cell"/>.
/// </summary>
/// <param name="writeAheadLogOptions">Specifies write-ahead logging behavior. Valid values are CellAccessOptions.StrongLogAhead(default) and CellAccessOptions.WeakLogAhead. Other values are ignored.</param>
/// <param name="cellId">A 64-bit cell id.</param>
/// <param name="cell">The cell to be saved.</param>
public void SaveGenericCell(CellAccessOptions writeAheadLogOptions, long cellId, ICell cell)
{
m_SaveGenericCell_CellAccessOptions_long_ICell(this, writeAheadLogOptions, cellId, cell);
}
/// <summary>DataSet导出到Excel的MemoryStream</summary>
/// <param name="ds">源DataSet</param>
/// <param name="strHeaderTexts">表格头文本值集合</param>
/// <param name="sheetCombineColIndexs">每个表格的要垂直合并的列的序号如：{"0,1","2"}表示表1的第0和1列进行合并,表2的第2列进行合并</param>
/// <returns></returns>
public static MemoryStream ExportDS(DataSet ds, List <string> strHeaderTexts, List <string> sheetCombineColIndexs)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.jack.com/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Jack"; //填加xls文件作者信息
si.ApplicationName = "jackExcel"; //填加xls文件创建程序信息
si.LastAuthor = "Jack"; //填加xls文件最后保存者信息
si.Comments = "Jack导出的excel"; //填加xls文件作者信息
si.Title = "Jack导出的excel"; //填加xls文件标题信息
si.Subject = "Jack导出的excel"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss.fff");
for (int i = 0; i < ds.Tables.Count; i++)
{
#region 填充单个sheet
int contentRowStartIndex = 0;//表格内容的内容数据起始行,用于合并同列多行之间的合并
sheet = workbook.CreateSheet(ds.Tables[i].TableName.StartsWith("Table") ? "Sheet" + (i + 1).ToString() : ds.Tables[i].TableName) as HSSFSheet;
sheet.DefaultRowHeight = 22 * 20;
DataTable dtSource = ds.Tables[i];
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];//保存列的宽度
foreach (DataColumn item in dtSource.Columns)
{
//先根据列名的字符串长度初始化所有的列宽
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int ii = 0; ii < dtSource.Rows.Count; ii++)
{
//遍历数据内容,根据每一列的数据最大长度设置列宽
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[ii][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
#region 填充表头,列头,数据内容
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
if (strHeaderTexts != null && strHeaderTexts.Count - 1 >= i)
{
if (!string.IsNullOrWhiteSpace(strHeaderTexts[i]))
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderTexts[i]);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 15;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex++;
//headerRow.Dispose();
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headerRow.HeightInPoints = 23;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 11;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽，这里我多加了10个字符的长度
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 10) * 256);
}
rowIndex++;
contentRowStartIndex = rowIndex;//记住数据内容的起始行
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
dataRow.HeightInPoints = 22;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
#endregion
Hashtable ht = new Hashtable();
#region 列中多行之间的合并
if (sheetCombineColIndexs != null && sheetCombineColIndexs.Count > i)
{
List <int> combineColIndexs = new List <int>();
string[] strarr = sheetCombineColIndexs[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in strarr)
{
combineColIndexs.Add(int.Parse(item));
}
for (int j = contentRowStartIndex; j < rowIndex; j++)
{
for (int jj = 0; jj < combineColIndexs.Count; jj++)
{
int cloIndex = combineColIndexs[jj];
if (j == contentRowStartIndex)
{
Entry entry = new Entry();
entry.startIndex = contentRowStartIndex;
object o = GetCellValue(sheet.GetRow(contentRowStartIndex).Cells[cloIndex]);
entry.combineValue = o == null ? "" : o.ToString();
ht.Add(cloIndex, entry);
continue;
}
object obj = GetCellValue(sheet.GetRow(j).Cells[cloIndex]);
string value = obj == null ? "" : obj.ToString();
Entry en = (Entry)ht[cloIndex];
if (en.combineValue != value)
{
//如果发生不相等的情况则满足合并条件(最少是2行)就会合并
if (en.startIndex + 1 < j)
{
sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j - 1, cloIndex));
ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex];
ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cell.CellStyle = cellstyle;
}
en.combineValue = value;
en.startIndex = j;
}
else
{
//如果相等了,再判断是不是最后一行,如果是最后一行也要合并
if (j == rowIndex - 1)
{
sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j, cloIndex));
ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex];
ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cell.CellStyle = cellstyle;
}
}
}
}
}
#endregion
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// Adds the data.
/// </summary>
/// <returns>The data.</returns>
/// <param name="title">Title.</param>
/// <param name="value">Value.</param>
/// <param name="dic">Dic.</param>
public Type AddData(ICell title, ICell value, Dictionary <string, object> dic)
{
Type type = null;
if (title == null || value == null)
{
return(type);
}
if (dic == null)
{
dic = new Dictionary <string, object> ();
}
string temp = "";
Action innerAddData = () => {
int i;
long l;
float f;
double d;
if (int.TryParse(temp, out i))
{
type = typeof(int);
dic.Add(title.StringCellValue, i);
}
else if (long.TryParse(temp, out l))
{
type = typeof(long);
dic.Add(title.StringCellValue, l);
}
else if (float.TryParse(temp, out f))
{
type = typeof(float);
dic.Add(title.StringCellValue, f);
}
else if (double.TryParse(temp, out d))
{
type = typeof(double);
dic.Add(title.StringCellValue, d);
}
else
{
type = typeof(string);
dic.Add(title.StringCellValue, temp);
}
};
if (value.CellType == CellType.String)
{
temp = value.StringCellValue;
innerAddData();
}
else if (value.CellType == CellType.Numeric)
{
temp = value.NumericCellValue.ToString();
innerAddData();
}
else if (value.CellType == CellType.Boolean)
{
type = typeof(bool);
dic.Add(title.StringCellValue, value.BooleanCellValue);
}
return(type);
}
/// <summary>
/// Adds a new cell to the key-value store if the cell Id does not exist, or updates an existing cell in the key-value store if the cell Id already exists.
/// Note that the generic cell will be saved as a strongly typed cell. It can then be loaded into either a strongly-typed cell or a generic cell.
/// The <paramref name="cellId"/> overrides the cell id in <paramref name="cell"/>.
/// </summary>
/// <param name="cellId">A 64-bit cell id.</param>
/// <param name="cell">The cell to be saved.</param>
public void SaveGenericCell(long cellId, ICell cell)
{
m_SaveGenericCell_long_ICell(this, cellId, cell);
}
/// <summary>
/// Adds a new cell to the key-value store if the cell Id does not exist, or updates an existing cell in the key-value store if the cell Id already exists.
/// Note that the generic cell will be saved as a strongly typed cell. It can then be loaded into either a strongly-typed cell or a generic cell.
/// </summary>
/// <param name="cell">The cell to be saved.</param>
public void SaveGenericCell(ICell cell)
{
m_SaveGenericCell_ICell(this, cell);
}
/// <summary>
/// 将数据导入到Excel中(通过制定格式的模板)
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="excelTemplate"></param>
/// <param name="fileName"></param>
public static void DataTableToExcel(DataTable sourceTable, Stream excelTemplate, string fileName, int templateRowIndex = 1)
{
HSSFWorkbook workbook = new HSSFWorkbook(excelTemplate);
ISheet sheet = workbook.GetSheetAt(0);
IRow templateRow = sheet.GetRow(templateRowIndex);
List <KeyValuePair <object, bool> > rowTemplate = new List <KeyValuePair <object, bool> >();
int cellCount = templateRow.LastCellNum;
for (int i = templateRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = templateRow.GetCell(i);
object cellValue = null;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BOOLEAN:
cellValue = cell.BooleanCellValue;
break;
case CellType.ERROR:
cellValue = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cellValue = cell.CellFormula;
break;
case CellType.NUMERIC:
cellValue = cell.NumericCellValue;
break;
case CellType.BLANK:
case CellType.STRING:
case CellType.Unknown:
default:
cellValue = cell.StringCellValue;
break;
}
}
if (cellValue != null && cellValue.ToString().IndexOf("{") > -1 && cellValue.ToString().IndexOf("}") > -1)
{
cellValue = cellValue.ToString().Trim(' ', '\t', '{', '}');
rowTemplate.Add(new KeyValuePair <object, bool>(cellValue, true));
}
else
{
rowTemplate.Add(new KeyValuePair <object, bool>(cellValue, false));
}
}
int rowIndex = templateRowIndex;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
if (templateRow.RowStyle != null)
{
dataRow.RowStyle = templateRow.RowStyle;
}
for (int i = 0; i < rowTemplate.Count; i++)
{
KeyValuePair <object, bool> keyValue = rowTemplate[i];
ICell cell = dataRow.CreateCell(i);
if (keyValue.Value && sourceTable.Columns.Contains(keyValue.Key.ToString()))//
{
if (row[keyValue.Key.ToString()] is DateTime)
{
cell.SetCellValue(((DateTime)row[keyValue.Key.ToString()]).ToString("yyyy/MM/dd"));
}
else
{
cell.SetCellValue(row[keyValue.Key.ToString()].ToString());
}
}
else
{
if (keyValue.Key is bool)
{
cell.SetCellValue((bool)keyValue.Key);
}
else if (keyValue.Key is DateTime)
{
cell.SetCellValue((DateTime)keyValue.Key);
}
else if (keyValue.Key is string)
{
cell.SetCellValue((string)keyValue.Key);
}
else if (keyValue.Key is double)
{
cell.SetCellValue((double)keyValue.Key);
}
else if (keyValue.Key is IRichTextString)
{
cell.SetCellValue((IRichTextString)keyValue.Key);
}
else
{
cell.SetCellValue(keyValue.Key == null ? null : keyValue.Key.ToString());
}
}
}
rowIndex++;
}
using (FileStream destStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook.Write(destStream);
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
/**
* Should be able to write then read formulas with references
* to cells in other files, eg '[refs/airport.xls]Sheet1'!$A$2
* or 'http://192.168.1.2/[blank.xls]Sheet1'!$A$1 .
* Additionally, if a reference to that file is provided, it should
* be possible to Evaluate them too
* TODO Fix this to Evaluate for XSSF
* TODO Fix this to work at all for HSSF
*/
// [Test]
public void bug46670()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
// References to try
String ext = "xls";
if (!(wb is HSSFWorkbook))
{
ext += "x";
}
String refLocal = "'[test." + ext + "]Sheet1'!$A$2";
String refHttp = "'[http://example.com/test." + ext + "]Sheet1'!$A$2";
String otherCellText = "In Another Workbook";
// Create the references
ICell c1 = r1.CreateCell(0, CellType.Formula);
c1.CellFormula = (/*setter*/ refLocal);
ICell c2 = r1.CreateCell(1, CellType.Formula);
c2.CellFormula = (/*setter*/ refHttp);
// Check they were Set correctly
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Reload, and ensure they were serialised and read correctly
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r1 = s.GetRow(0);
c1 = r1.GetCell(0);
c2 = r1.GetCell(1);
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Try to Evalutate, without giving a way to Get at the other file
try
{
EvaluateCell(wb, c1);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
try
{
EvaluateCell(wb, c2);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
// Set up references to the other file
IWorkbook wb2 = _testDataProvider.CreateWorkbook();
wb2.CreateSheet().CreateRow(1).CreateCell(0).SetCellValue(otherCellText);
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add(refLocal, wb2.GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add(refHttp, wb2.GetCreationHelper().CreateFormulaEvaluator());
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.SetupReferencedWorkbooks(/*setter*/ evaluators);
// Try to Evaluate, with the other file
Evaluator.EvaluateFormulaCell(c1);
Evaluator.EvaluateFormulaCell(c2);
Assert.AreEqual(otherCellText, c1.StringCellValue);
Assert.AreEqual(otherCellText, c2.StringCellValue);
}
/// <summary>插入行</summary>
/// <param name="sheet">要插入行的sheet</param>
/// <param name="startindex">从这一行的前面插入(这一行开始包括这一行都会被整体向下移动rowcount)</param>
/// <param name="rowcount">插入的行数</param>
/// <param name="stylerow">被插入行采用的样式行的索引,注意这个索引行所在的位置应该位于插入起始行之上</param>
public static void InsertRow(ISheet sheet, int startindex, int rowcount, int styleindex)
{
IRow stylerow = sheet.GetRow(styleindex);
if (sheet.LastRowNum >= startindex)
{
//批量移动行
sheet.ShiftRows(startindex, sheet.LastRowNum, rowcount, true /*是否复制行高*/, false);
}
#region 对批量移动后空出的空行插，创建相应的行，并以样式行作为模板设置样式
for (int i = startindex; i < startindex + rowcount; i++)
{
IRow targetRow = null;
ICell sourceCell = null;
ICell targetCell = null;
targetRow = sheet.CreateRow(i);
targetRow.Height = stylerow.Height;
targetRow.HeightInPoints = stylerow.HeightInPoints;
targetRow.ZeroHeight = stylerow.ZeroHeight;
int mergeindex = -1;
for (int m = stylerow.FirstCellNum; m < stylerow.LastCellNum; m++)
{
sourceCell = stylerow.GetCell(m);
if (sourceCell == null)
{
if (mergeindex > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, mergeindex, m));
mergeindex = -1;
}
continue;
}
targetCell = targetRow.CreateCell(m);
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
if (sourceCell.IsMergedCell)
{
if (mergeindex > 0 && m + 1 < stylerow.LastCellNum)
{
continue;
}
else if (mergeindex > 0 && m + 1 == stylerow.LastCellNum)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, mergeindex, m));
mergeindex = -1;
}
else
{
mergeindex = m;
}
}
else
{
if (mergeindex > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, mergeindex, m));
mergeindex = -1;
}
}
}
}
IRow firstTargetRow = sheet.GetRow(startindex);
ICell firstSourceCell = null;
ICell firstTargetCell = null;
if (rowcount > 0)
{
//新添加的行应用样式
for (int m = stylerow.FirstCellNum; m < stylerow.LastCellNum; m++)
{
firstSourceCell = stylerow.GetCell(m);
if (firstSourceCell == null)
{
continue;
}
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
}
#endregion
}
/// 设置单元格值
/// <param name="cell"></param>
/// <param name="value"></param>
protected virtual void SetCellValue(ICell cell, object value)
{
if (null == cell)
{
return;
}
if (null == value)
{
cell.SetCellValue(string.Empty);
}
else
{
if (value.GetType().FullName.Equals("System.Byte[]"))
{
var pictureIdx = cell.Sheet.Workbook.AddPicture((Byte[])value, PictureType.PNG);
var anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColSpan;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
var patriarch = cell.Sheet.CreateDrawingPatriarch();
var pic = patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
var valueTypeCode = Type.GetTypeCode(value.GetType());
switch (valueTypeCode)
{
case TypeCode.String: //字符串类型
cell.SetCellValue(Convert.ToString(value));
break;
case TypeCode.DateTime: //日期类型
cell.SetCellValue(Convert.ToDateTime(value));
break;
case TypeCode.Boolean: //布尔型
cell.SetCellValue(Convert.ToBoolean(value));
break;
case TypeCode.Int16: //整型
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Byte:
case TypeCode.Single: //浮点型
case TypeCode.Double:
case TypeCode.Decimal:
case TypeCode.UInt16: //无符号整型
case TypeCode.UInt32:
case TypeCode.UInt64:
cell.SetCellValue(Convert.ToDouble(value));
break;
default:
cell.SetCellValue(string.Empty);
break;
}
}
}
}
