public void Channel(IWorkbook myWorkbook)
{
ISheet worksheet = myWorkbook.GetSheet("CHANNEL");
int lastRow = worksheet.LastRowNum;
Debug.Print(myWorkbook.NumberOfSheets.ToString());
Debug.Print("lastRow " + lastRow);
for (int i = 1; i <= lastRow; i++)
{
IRow row = worksheet.GetRow(i);
ChannelData channel = new ChannelData();
channel.Channel = Convert.ToInt32(GetStringCellData(CellNameV1.DiskNo, row?.GetCell((int)CellNameChannel.Channel)));
channel.Name = GetStringCellData(CellNameChannel.Name, row?.GetCell((int)CellNameChannel.Name));
channel.BroadcastKind = GetStringCellData(CellNameChannel.BroadcastKind, row?.GetCell((int)CellNameChannel.BroadcastKind));
channel.RipId = GetStringCellData(CellNameChannel.RipId, row?.GetCell((int)CellNameChannel.RipId));
channel.VideoRate = GetStringCellData(CellNameChannel.VideoRate, row?.GetCell((int)CellNameChannel.VideoRate));
channel.VoiceRate = GetStringCellData(CellNameChannel.VoiceRate, row?.GetCell((int)CellNameChannel.VoiceRate));
channel.Remark = GetStringCellData(CellNameChannel.Remark, row?.GetCell((int)CellNameChannel.Remark));
DbExport(channel, new DbConnection());
//Debug.Print(i + " " + channel.Channel + " Name:" + channel.Name + " Kind:" + channel.BroadcastKind + " RipId:" + channel.RipId + " VideoRate:" + channel.VideoRate + " VoiceRate:" + channel.VoiceRate);
//Debug.Print(" Remark:" + channel.Remark);
}
}
public void TvV1(IWorkbook myWorkbook)
{
ISheet worksheet = myWorkbook.GetSheet("TV録画");
int lastRow = worksheet.LastRowNum;
Debug.Print(myWorkbook.NumberOfSheets.ToString());
Debug.Print("lastRow " + lastRow);
for (int i = 1; i <= lastRow; i++)
{
IRow row = worksheet.GetRow(i);
Record record = new Record();
record.DiskNo = GetStringCellData(CellNameV1.DiskNo, row?.GetCell((int)CellNameV1.DiskNo));
record.Seq = GetStringCellData(CellNameV1.Seq, row?.GetCell((int)CellNameV1.Seq));
record.RipStatus = GetStringCellData(CellNameV1.RipStatus, row?.GetCell((int)CellNameV1.RipStatus));
string onAirDate = GetStringCellData(CellNameV1.OnAirDate, row?.GetCell((int)CellNameV1.OnAirDate));
record.ProgramId = GetStringCellData(CellNameV1.ProgramId, row?.GetCell((int)CellNameV1.ProgramId));
string programName = GetStringCellData(CellNameV1.ProgramDisplay, row?.GetCell((int)CellNameV1.ProgramDisplay));
string startTime = GetStringCellData(CellNameV1.StartTime, row?.GetCell((int)CellNameV1.StartTime));
record.Duration = GetStringCellData(CellNameV1.Duration, row?.GetCell((int)CellNameV1.Duration));
record.Detail = GetStringCellData(CellNameV1.Detail, row?.GetCell((int)CellNameV1.Detail));
record.SetOnAirDate(onAirDate);
DbExport(record, new DbConnection());
//Debug.Print(i + " " + record.DiskNo + " Seq:" + record.Seq + " Rip:" + record.RipStatus + " onAirDate:" + record.OnAirDate + " ProgramId:" + record.ProgramId + " programName:" + programName);
//Debug.Print(" startTime:" + startTime + " duration:" + record.Duration + " detail:" + record.Detail);
}
}
public void TvV2(IWorkbook myWorkbook)
{
ISheet worksheet = myWorkbook.GetSheet("TV録画2");
int lastRow = worksheet.LastRowNum;
Debug.Print(myWorkbook.NumberOfSheets.ToString());
Debug.Print("lastRow " + lastRow);
string beforeDiskNo = "";
for (int i = 1; i <= lastRow; i++)
{
IRow row = worksheet.GetRow(i);
Record record = new Record();
record.DiskNo = GetStringCellData(CellName.DiskNo, row?.GetCell((int)CellName.DiskNo));
record.Seq = GetStringCellData(CellName.Seq, row?.GetCell((int)CellName.Seq));
record.RipStatus = GetStringCellData(CellName.RipStatus, row?.GetCell((int)CellName.RipStatus));
string onAirDate = GetStringCellData(CellName.OnAirDate, row?.GetCell((int)CellName.OnAirDate));
record.ProgramId = GetStringCellData(CellName.ProgramId, row?.GetCell((int)CellName.ProgramId));
string programName = GetStringCellData(CellName.ProgramDisplay, row?.GetCell((int)CellName.ProgramDisplay));
string startTime = GetStringCellData(CellName.StartTime, row?.GetCell((int)CellName.StartTime));
record.Duration = GetStringCellData(CellName.Duration, row?.GetCell((int)CellName.Duration));
record.Detail = GetStringCellData(CellName.Detail, row?.GetCell((int)CellName.Detail));
record.SetOnAirDate(onAirDate, startTime);
if (record.ProgramId.Length <= 0 && onAirDate.Length <= 0)
{
continue;
}
if (record.DiskNo == null || record.DiskNo.Length <= 0)
{
if (beforeDiskNo.Length > 0)
{
record.DiskNo = beforeDiskNo;
}
}
else
{
beforeDiskNo = "";
}
DbExport(record, new DbConnection());
if (beforeDiskNo.Length <= 0)
{
beforeDiskNo = record.DiskNo + "(TEMP)";
}
//Debug.Print(i + " " + record.DiskNo + " Seq:" + record.Seq + " Rip:" + record.RipStatus + " onAirDate:" + record.OnAirDate + " ProgramId:" + record.ProgramId + " programName:" + programName);
//Debug.Print(" startTime:" + startTime + " duration:" + record.Duration + " detail:" + record.Detail);
}
}
/// <summary>
/// 获取最大行数和表头数据
/// </summary>
/// <param name="book"></param>
/// <param name="sheetIndex"></param>
/// <returns></returns>
public static KeyValuePair <int, List <string> > GetMaxRowNumAndHead(this IWorkbook book, int sheetIndex = 0)
{
var header = new List <string>();
ISheet sheet = book.GetSheetAt(sheetIndex);
IRow headerRow = sheet.GetRow(0);
var rowNum = headerRow.LastCellNum;
for (int i = 0; i <= rowNum; i++)
{
if (!string.IsNullOrWhiteSpace(headerRow?.GetCell(i)?.ToString()))
{
header.Add(headerRow?.GetCell(i)?.ToString());
}
}
return(new KeyValuePair <int, List <string> >(sheet.LastRowNum, header));
}
/// <summary>
/// セルのタイプを取得
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <returns>CellType</returns>
protected CellType?GetCellType(ISheet sheet, int columnIndex, int rowIndex)
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
return(cell?.CellType);
}
private Dictionary<string, object> ReadRow(IRow row, KeyValuePair<int, string>[] titles)
{
var data = new Dictionary<string, object>();
foreach (var title in titles)
{
var cell = row.GetCell(title.Key);
if (cell == null)
{
data.Add(title.Value, string.Empty);
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
data.Add(title.Value, cell.NumericCellValue);
break;
case CellType.String:
data.Add(title.Value, cell.StringCellValue);
break;
case CellType.Blank:
data.Add(title.Value, string.Empty);
break;
default:
throw new Exception(string.Format("Invalid excel cell type: {0} ({1}.{2}.{3})", cell.CellType, cell.Row.Sheet.SheetName, cell.RowIndex, cell.ColumnIndex));
}
}
return data;
}
/// <summary>
/// セルの内容を取得する
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <returns>取得文字列</returns>
protected string ReadCellString(ISheet sheet, int columnIndex, int rowIndex)
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
return(cell?.StringCellValue);
}
/// <summary>
/// 转换列值
/// </summary>
/// <param name="row">行</param>
/// <param name="columnIndex">当前列下标,起始0</param>
/// <param name="valueType">值类型/属性类型，如 PropertyInfo.PropertyType ，typeof(int?)，typeof(bool),typeof(string)</param>
/// <returns></returns>
public virtual object ConverterCellValue(IRow row, int columnIndex, Type valueType)
{
ICell cell = row?.GetCell(columnIndex);
if (cell == null)
{
return(null);
}
object cellValue = GetMergedCellValue(cell.Sheet, cell);
if (string.IsNullOrWhiteSpace(cellValue?.ToString()))
{
return(cellValue);
}
if (valueType.IsDateTime())
{
cellValue = cellValue.GetTypedCellValue <DateTime>();
}
else if (valueType.IsTimeSpan())
{
cellValue = cellValue.GetTypedCellValue <TimeSpan>();
}
return(TypeDescriptor.GetConverter(valueType).ConvertFromInvariantString(cellValue.ToString()));
}
static HashSet <string> GetCfg(XIHLanguage language, ISheet sheet, int col)
{
LanguageCfg cfg = AssetDatabase.LoadAssetAtPath <LanguageCfg>($"Assets/Resources/Config/Localization/{language}");
if (cfg == null)
{
cfg = ScriptableObject.CreateInstance <LanguageCfg>();
AssetDatabase.CreateAsset(cfg, $"Assets/Resources/Config/Localization/{language}.asset");
}
cfg.keyWords = new List <KeyWord>();
int rowNum = 1;
IRow row = sheet.GetRow(rowNum);
string key = row?.GetCell(0)?.StringCellValue;
HashSet <string> keys = new HashSet <string>();
while (!string.IsNullOrEmpty(key))
{
key = key.ToLower();
if (keys.Contains(key))
{
Debug.LogWarning($"包含重复Key:{key},在表中位置【{rowNum + 1}】行");
}
else
{
string word = row.GetCell(col)?.StringCellValue;
if (string.IsNullOrEmpty(word))
{
Debug.LogWarning($"Key:{key}所对应word为空,在表中位置【{rowNum + 1}】行，【{col + 1}】列");
}
else
{
cfg.keyWords.Add(new KeyWord()
{
key = key, word = word
});
}
}
keys.Add(key);
row = sheet.GetRow(++rowNum);
key = row?.GetCell(0)?.StringCellValue;
}
EditorUtility.SetDirty(cfg);
AssetDatabase.SaveAssets();
return(keys);
}
public void Program(IWorkbook myWorkbook)
{
ISheet worksheet = myWorkbook.GetSheet("番組名");
int lastRow = worksheet.LastRowNum;
Debug.Print(myWorkbook.NumberOfSheets.ToString());
Debug.Print("lastRow " + lastRow);
for (int i = 1; i <= lastRow; i++)
{
IRow row = worksheet.GetRow(i);
Program program = new Program();
program.ChannelId = GetStringCellData(CellNameProgram.Id, row?.GetCell((int)CellNameProgram.Id));
program.Name = GetStringCellData(CellNameProgram.Name, row?.GetCell((int)CellNameProgram.Name));
program.AbbreviationName = GetStringCellData(CellNameProgram.Name, row?.GetCell((int)CellNameProgram.Name));
program.Kind = GetStringCellData(CellNameProgram.Kind, row?.GetCell((int)CellNameProgram.Kind));
program.RelationId = GetStringCellData(CellNameProgram.RelationId, row?.GetCell((int)CellNameProgram.RelationId));
program.DateKind = GetStringCellData(CellNameProgram.DateKind, row?.GetCell((int)CellNameProgram.DateKind));
string onAirStart = GetStringCellData(CellNameProgram.OnAirStart, row?.GetCell((int)CellNameProgram.OnAirStart));
string onAirEnd = GetStringCellData(CellNameProgram.OnAirEnd, row?.GetCell((int)CellNameProgram.OnAirEnd));
program.Detail = GetStringCellData(CellNameProgram.Detail, row?.GetCell((int)CellNameProgram.Detail));
program.SetOnAirStart(onAirStart);
program.SetOnAirEnd(onAirEnd);
DbExport(program, new DbConnection());
Debug.Print(i + " " + program.ChannelId + " Name:" + program.Name + " AbbreviationName:" + program.AbbreviationName + " Kind:" + program.Kind + " DateKind:" + program.DateKind);
Debug.Print(" RelationId:" + program.RelationId + " OnAirDuration:" + program.OnAirStart + "～" + program.OnAirEnd);
Debug.Print(" Detail:" + program.Detail);
}
}
public static string StringValue(this IRow row, int column, string defaultValue = null)
{
if (row == null)
{
return(defaultValue);
}
return(row?.GetCell(column)?.StringCellValue ?? defaultValue);
}
public static string GetCell(IRow row, int cell)
{
ICell iCell = row?.GetCell(cell);
if (iCell != null)
{
return(GetCell(iCell));
}
return(string.Empty);
}
/// <summary>
/// セルのタイプが日付型か判定
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <returns>判定結果</returns>
protected bool CheckCellTypeDate(ISheet sheet, int columnIndex, int rowIndex)
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
if (cell?.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
return(true);
}
return(false);
}
public static void Main(String[] args)
{
Iworkbook workbook = WorkbookFactory.Create("./test.xlsx");
ISheet worksheet = workbook.GetSheetAt(0);
int lastRow = worksheet.LastRowNum;
for (int i = 0; i < lastRow; i++)
{
IRow row = worksheet.GetRow(i);
ICell cell = row?.GetCell(0);
System.Console.WriteLine(cell?.StringCellValue);
}
}
private static string GetCellString(IRow row, int i, IFormulaEvaluator formulaEvaluator)
{
ICell cell = row?.GetCell(i);
if (cell?.CellType == CellType.Formula)
{
formulaEvaluator.ClearAllCachedResultValues();
return $"{formulaEvaluator.EvaluateInCell(cell)}";
}
else
{
return cell?.ToString() ?? "";
}
}
protected DataTransactionMessage CheckAndInsertData(IRow row)
{
var result = new DataTransactionMessage(row.RowNum);
var currentInformation = new Information();
for (var i = 0; i < Columns.Length; i++)
{
var cell = row.GetCell(i);
var currentCell = string.Empty;
if (cell != null)
{
currentCell = cell.ToString();
}
if (!IsValid(currentCell, i, result, currentInformation))
{
break;
}
}
InsertData(result, currentInformation);
return result;
}
/// <summary>
/// 文字列として値を取得
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <param name="value">文字列型変数</param>
/// <returns>取得の成否</returns>
protected bool TryReadCell(ISheet sheet, int columnIndex, int rowIndex, out string value)
{
try
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
value = cell?.StringCellValue;
if (value == null)
{
return(false);
}
return(true);
}
catch (Exception)
{
value = string.Empty;
return(false);
}
}
public static void SetBorderStyle(IWorkbook workbook, IRow row, int columns)
{
row.Height = 14 * 30;
IFont font18 = workbook.CreateFont();
font18.FontName = "宋体";
font18.FontHeightInPoints = 10;
for (int i = 0; i < columns; i++)
{
ICellStyle style12 = workbook.CreateCellStyle();
style12.SetFont(font18);
//style12.Alignment = HorizontalAlignment.CENTER;
style12.VerticalAlignment = VerticalAlignment.CENTER;
style12.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
row.GetCell(i).CellStyle = style12;
}
}
public static void DumpConsole(string excelFileName, int n)
{
try
{
IWorkbook workbook = WorkbookFactory.Create(excelFileName);
ISheet worksheet = workbook.GetSheetAt(n);
int lastRow = worksheet.LastRowNum;
for (int i = 0; i <= lastRow; i++)
{
IRow row = worksheet.GetRow(i);
int lastCell = row.LastCellNum;
string[] cellValues = new string[lastCell];
for (int j = 0; j < lastCell; j++)
{
ICell cell = row?.GetCell(j);
if (cell != null)
{
switch (cell?.CellType)
{
case CellType.String:
cellValues[j] = cell?.StringCellValue;
break;
case CellType.Numeric:
cellValues[j] = cell?.NumericCellValue.ToString();
break;
default:
break;
}
}
}
Console.WriteLine(String.Join(",", cellValues));
}
Console.WriteLine();
}
catch
{
}
}
/// <summary>
/// 日時型として値を取得
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <param name="value">日時型変数</param>
/// <returns>取得の成否</returns>
protected bool TryReadCell(ISheet sheet, int columnIndex, int rowIndex, out DateTime value)
{
try
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
DateTime?result = cell?.DateCellValue;
if (!result.HasValue)
{
value = new DateTime();
return(false);
}
value = (DateTime)result;
return(true);
}
catch (Exception)
{
value = new DateTime();
return(false);
}
}
public static void SetHeaderStyle(IWorkbook workbook, IRow row, int columns)
{
row.Height = 16 * 30;
IFont font18 = workbook.CreateFont();
font18.FontName = "宋体";
font18.FontHeightInPoints = 12;
font18.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
for (int i = 0; i < columns; i++)
{
ICellStyle style12 = workbook.CreateCellStyle();
style12.SetFont(font18);
// style12.Alignment = HorizontalAlignment.CENTER;
style12.VerticalAlignment = VerticalAlignment.CENTER;
style12.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style12.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//style12.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index2;
row.GetCell(i).CellStyle = style12;
}
}
/// <summary>
/// decimalとして値を取得
/// </summary>
/// <param name="sheet">対象シート</param>
/// <param name="columnIndex">列</param>
/// <param name="rowIndex">行</param>
/// <param name="value">decimal型変数</param>
/// <returns>取得の成否</returns>
protected bool TryReadCell(ISheet sheet, int columnIndex, int rowIndex, out decimal value)
{
try
{
IRow row = sheet?.GetRow(rowIndex);
ICell cell = row?.GetCell(columnIndex);
double?result = cell?.NumericCellValue;
value = 0;
if (!result.HasValue)
{
return(false);
}
value = (decimal)result;
return(true);
}
catch (Exception)
{
value = 0;
return(false);
}
}
public static object getCellValue_ForAvgMonth(IRow row, int cell_index)
{
ICell cell = row.GetCell(cell_index);
object result = null;
if (cell == null)
return result;
CellType ctype = cell.CellType;
if (ctype == CellType.Blank)//取得空白
result = null;
else if (ctype == CellType.String)//字串
result = cell.StringCellValue.ToString() == "" ? null : cell.StringCellValue.ToString();
else if (ctype == CellType.Numeric)//數字
result = cell.NumericCellValue;
else if (ctype == CellType.Boolean)//布林
result = null;
else
result = null;
return result;
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
{
ISheet sheet = cell.Sheet;
IWorkbook wb = sheet.Workbook;
IRow row = cell.Row;
int column = cell.ColumnIndex;
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
return(-1);
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
ICellStyle style = cell.CellStyle;
CellType cellType = cell.CellType;
IFont defaultFont = wb.GetFontAt((short)0);
Font windowsFont = IFont2Font(defaultFont);
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.Formula)
{
cellType = cell.CachedFormulaResultType;
}
IFont font = wb.GetFontAt(style.FontIndex);
//AttributedString str;
//TextLayout layout;
double width = -1;
using (Bitmap bmp = new Bitmap(1, 1))
using (Graphics g = Graphics.FromImage(bmp))
{
if (cellType == CellType.String)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
}
else
{
//width = Math.Max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.Numeric)
{
// Try to get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString();
}
}
else if (cellType == CellType.Boolean)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
//width = Math.max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
return(width);
}
private ITicket MapRowToTicketObject(IRow row)
{
try
{
ITicket ticket = new Ticket();
ticket.ActualStake = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.ActualStake)));
ticket.AgentComm = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.AgentComm)));
ticket.AgentDiscount = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.AgentDiscount)));
ticket.AgentPositionTaking = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.AgentPositionTaking)));
ticket.AgentWinlost = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.AgentWinlost)));
ticket.AwayId = Convert.ToInt64(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.AwayId)));
ticket.BetCheck = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.BetCheck));
ticket.BetId = Convert.ToInt64(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.BetId)));
ticket.BetTeam = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.BetTeam));
ticket.BetTypeId = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.BetTypeId)));
ticket.Comm = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Comm)));
ticket.CommStatus = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.CommStatus));
ticket.CustId = Convert.ToInt64(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.CustId)));
ticket.EventDate = Convert.ToDateTime(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.EventDate)));
ticket.EventStatus = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.EventStatus));
ticket.Handicap1 = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Handicap1)));
ticket.Handicap2 = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Handicap2)));
ticket.HomeId = Convert.ToInt64(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.HomeId)));
ticket.IP = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.IP));
ticket.IsLive = Convert.ToBoolean(Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.IsLive))));
ticket.IsNeutral = Convert.ToBoolean(Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.IsNeutral))));
ticket.LeagueId = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.LeagueId)));
ticket.LiveAwayScore = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.LiveAwayScore)));
ticket.LiveHomeScore = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.LiveHomeScore)));
ticket.MasterDiscount = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MasterDiscount)));
ticket.MasterOdds = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MasterOdds)));
ticket.MasterPositionTaking = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MasterPositionTaking)));
ticket.MasterWinlost = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MasterWinlost)));
ticket.MatchCode = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MatchCode));
ticket.MatchId = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.MatchId)));
ticket.Odds = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Odds)));
ticket.OddsType = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.OddsType));
ticket.PlayerComm = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.PlayerComm)));
ticket.Race = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Race));
ticket.RefNo = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.RefNo));
ticket.ShowTime = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.ShowTime));
ticket.SportTypeId = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.SportTypeId)));
ticket.Stake = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Stake)));
ticket.Status = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Status));
ticket.SuperComm = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.SuperComm)));
ticket.SuperDiscount = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.SuperDiscount)));
ticket.SuperPositionTaking = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.SuperPositionTaking)));
ticket.SuperWinlost = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.SuperWinlost)));
ticket.TransDate = Convert.ToDateTime(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.TransDate)));
ticket.TransId = Convert.ToInt64(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.TransId)));
ticket.UserName = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.UserName));
ticket.Winlost = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.Winlost)));
ticket.WinlostDate = Convert.ToDateTime(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.WinlostDate)));
ticket.TransDesc = this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.TransDesc));
try
{
ticket.OddsSpread = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketTemplateColumnIndex.OddsSpread)));
}
catch
{
}
return ticket;
}
catch
{
}
return null;
}
private static string GetCellString(IRow row, int i)
{
return(GetCellValue(row?.GetCell(i)));
}
/**
* @return <code>null</code> if cell is missing, empty or blank
*/
private static String GetTargetFunctionName(IRow r)
{
if (r == null)
{
System.Console.Error.WriteLine("Warning - given null row, can't figure out function name");
return null;
}
ICell cell = r.GetCell(SS.COLUMN_INDEX_FUNCTION_NAME);
if (cell == null)
{
System.Console.Error.WriteLine("Warning - Row " + r.RowNum + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name");
return null;
}
if (cell.CellType == CellType.BLANK)
{
return null;
}
if (cell.CellType == CellType.STRING)
{
return cell.RichStringCellValue.String;
}
throw new AssertionException("Bad cell type for 'function name' column: ("
+ cell.CellType + ") row (" + (r.RowNum + 1) + ")");
}
private static ICell GetExpectedValueCell(IRow row, int columnIndex)
{
if (row == null)
{
return null;
}
return row.GetCell(columnIndex);
}
/// <summary>
/// Excelファイルに変数の値をセットする
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
internal void SetValueFromExcelFile(string filePath, string sheetName)
{
IWorkbook workbook = null;
try
{
string ext = System.IO.Path.GetExtension(filePath).ToUpper();
workbook = WorkbookFactory.Create(filePath);
ISheet sheet = workbook.GetSheet(sheetName);
int lastRow = sheet.LastRowNum;
int lastCol = 0;
for (int i = 0; i <= lastRow; i++)
{
IRow row = sheet.GetRow(i);
int l = row.LastCellNum;
if (lastCol < l)
{
lastCol = l;
}
}
if (lastCol > ColumnCount)
{
ColumnCount = lastCol;
}
ValueList = new List <List <string> >();
for (int i = 0; i <= lastRow; i++)
{
IRow row = sheet.GetRow(i);
int last = row.LastCellNum;
ValueList.Add(GetNewStringList("", lastCol));
for (int i2 = 0; i2 < lastCol; i2++)
{
ValueList[i][i2] = "";
if (last > i2)
{
ICell cell = row?.GetCell(i2);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
case CellType.Error:
case CellType.Formula:
case CellType.Unknown:
ValueList[i][i2] = "";
break;
case CellType.Boolean:
ValueList[i][i2] = "" + cell.BooleanCellValue.ToString();
break;
case CellType.String:
ValueList[i][i2] = "" + cell.StringCellValue;
break;
case CellType.Numeric:
ValueList[i][i2] = "" + cell.NumericCellValue;
break;
}
}
}
}
}
workbook.Close();
}
catch (Exception ex)
{
if (workbook != null)
{
workbook.Close();
}
throw Program.ThrowException(ex);
}
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0); //第一行
int cellCount = firstRow.LastCellNum; //列数
//构建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
// CellType(Unknown = -1, Numeric = 0, String = 1, Formula = 2, Blank = 3, Boolean = 4, Error = 5,);
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
{
dataRow[j] = cell.DateCellValue;
}
else
{
dataRow[j] = cell.NumericCellValue;
}
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return(dataTable);
}
catch (Exception)
{
if (fs != null)
{
fs.Close();
}
return(null);
}
}
private static void ProcessSpecificationAttributes(IRow source, ProductInputRow inputRow,
int specificationAtrributesIdexStart, int specificationAtrributesIdexEnd, IRow header)
{
if (!inputRow.HasSpecificationAttributes.InvariantEquals("yes")) return;
for (int i = specificationAtrributesIdexStart; i <= specificationAtrributesIdexEnd; i += 2)
{
ICell attrNameCell = header.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
attrNameCell.SetCellType(CellType.String);
string attrName = attrNameCell.StringCellValue.Trim();
//var mapAttr = InputRowMap.FirstOrDefault(p => p.Value == attrName);
//if (!attrName.InvariantEquals(mapAttr.Value)) continue;
ICell attrCell = source.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
attrCell.SetCellType(CellType.String);
string attrValue = attrCell.StringCellValue.Trim();
if (attrValue.InvariantEquals("n/a")) continue;
ICell settingsCell = source.GetCell(i + 1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
settingsCell.SetCellType(CellType.String);
string[] settingsValue = settingsCell.StringCellValue.Trim().Split('|');
foreach (string attrVal in attrValue.Split('|'))
{
inputRow.ProductSpecificationAttributeInput.Add(new SpecificationAttributeInput
{
Name = attrName.Trim(),
IncludeInFilter = settingsValue[0],
ShowOnProductPage = settingsValue[1],
Value = attrVal.Trim()
});
}
}
}
private static void ProcessProductBase(IRow source, int specificationAtrributesIdexStart,
List<PropertyInfo> props,
ProductInputRow inputRow)
{
for (int i = 0; i < specificationAtrributesIdexStart; i++)
{
ICell cell = source.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.SetCellType(CellType.String);
string cellValue = cell.StringCellValue.Trim();
PropertyInfo prop = props.First(x => x.Name == InputRowMap[i]);
prop.SetValue(inputRow, cellValue, 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="row">行对象</param>
/// <param name="cols">列集合</param>
/// <param name="useCellType">是否使用单元格的数据类型格式化数据，如果列中的数据类型都是一致的可以设置为true，否则使用DataTable中列的数据类型读取数据。</param>
/// <returns></returns>
public object[] ReadRow(IRow row, DataColumnCollection cols, bool useCellType)
{
if (cols.Count == 0)
{
return null;
}
var lastCellNum = row.FirstCellNum + cols.Count - 1;
int i = 0;
object[] data = new object[cols.Count];
for (int j = row.FirstCellNum; j <= lastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell != null)
{
CellDataType cellDataType = CellDataType.Text;
if (useCellType)
{
cellDataType = GetCellDataTypeByCell(cell);
}
else
{
cellDataType = GetCellDataTypeByColumn(cols[i]);
}
data[i] = ReadCell(cell, cellDataType);
}
i++;
}
return data;
}
/// <summary>
/// 从Row实例获取标题
/// </summary>
/// <param name="row">Row实例</param>
/// <param name="startColNumber">起始列号</param>
/// <returns>标题列数组</returns>
public DataColumn[] ReadTitle(IRow row, int? startColNumber = null)
{
if (!startColNumber.HasValue)
{
startColNumber = row.FirstCellNum;
}
int cellCount = row.LastCellNum - startColNumber.Value;
DataColumn[] cols = new DataColumn[cellCount];
int j = 0;
for (int i = startColNumber.Value; i < row.LastCellNum; i++)
{
DataColumn column = new DataColumn(row.GetCell(i).StringCellValue);
cols[j] = column;
j++;
}
return cols;
}
/// <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);
}
private static double GetDoubleCell(IRow row, int colIx)
{
ICell cell = row.GetCell(colIx);
if (cell == null)
{
throw new SystemException("No cell found at column " + colIx);
}
double dVal = cell.NumericCellValue;
return dVal;
}
/// <summary>
/// 读取excel里面数据信息
/// </summary>
/// <param name="excel">excel文件路径</param>
/// <returns></returns>
public static DataTable NPOIReadExcel(string excel)
{
FileStream file = new FileStream(excel, FileMode.Open, FileAccess.Read);
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
IWorkbook workbook = null;
if (excel.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (excel.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(file);
}
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
//获取sheet的首行
IRow headerRow = sheet.GetRow(0);
//一行最后一个方格的编号,即总的列数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//最后一列的标号,即总的行数
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum + 1; i++)
{
IRow row = sheet.GetRow(i);
//这一句很关键，因为没有数据的行默认是null
if (row == null)
{
continue;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
object value = null;
//同理，没有数据的单元格都默认是null
if (cell != null && cell.CellType != CellType.BLANK)
{
switch (cell.CellType)
{
case CellType.STRING:
string str = cell.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
value = str.ToString();
}
else
{
value = string.Empty;
}
break;
case CellType.NUMERIC:
// Date comes here
var iCellStyle = cell.CellStyle;
short format = iCellStyle.DataFormat;
//日期
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else if (format == 20 || format == 32)
{
value = cell.DateCellValue.ToString("HH:mm");
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue;
break;
case CellType.FORMULA:
{
switch (cell.CachedFormulaResultType)
{
case CellType.BOOLEAN:
value = cell.BooleanCellValue;
break;
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.STRING:
string cellstr = cell.StringCellValue;
if (!string.IsNullOrEmpty(cellstr))
{
value = cellstr.ToString();
}
else
{
value = null;
}
break;
case CellType.Unknown:
case CellType.BLANK:
break;
default:
value = string.Empty;
break;
}
break;
}
default:
value = cell.StringCellValue;
break;
}
dataRow[j] = value;
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return(table);
}
/**
* Get a specific cell from a row. If the cell doesn't exist, then create it.
*
*@param row The row that the cell is part of
*@param columnIndex The column index that the cell is in.
*@return The cell indicated by the column.
*/
public static ICell GetCell(IRow row, int columnIndex)
{
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
return cell;
}
/// <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);
}
}
/**
*
* @return a constant from the local Result class denoting whether there were any Evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(HSSFFormulaEvaluator Evaluator, String targetFunctionName,
IRow formulasRow, IRow expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
short endcolnum = (short)formulasRow.LastCellNum;
// iterate across the row for all the Evaluation cases
for (int colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
ICell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != CellType.FORMULA)
{
continue;
}
CellValue actualValue = Evaluator.Evaluate(c);
ICell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (AssertionException e)
{
_EvaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return result;
}
public void ReadDataToExcel()
{
foreach (var item in sheetData)
{
int row = item.Key;
List<ProductTable> productTableList = new List<ProductTable>();
List<PromotionTable> promotionTablesList = new List<PromotionTable>();
PaymentSlipInfo payment = new PaymentSlipInfo();
ProductTable productTable = new ProductTable();
//PromotionTable promotionTable = new PromotionTable();
//int increase = 0;
//Lấy ghi chú hoặc + đến = bao nhiều xác định
while (row != item.Key + 4)
{
//Lấy đầu tiên là hắn row
IRow iRow = sheet.GetRow(row);
if (row == item.Key)
{
ICell cellE = iRow.GetCell((int)ExcelColumnEnum.E);
ICell cellQ = iRow.GetCell((int)ExcelColumnEnum.Q);
ICell cellAB = iRow.GetCell((int)ExcelColumnEnum.AB);
//
payment.orderNumber = (cellE.StringCellValue);
payment.dateCreate = (cellQ.ToString());
payment.NVBH = (cellAB.StringCellValue);
}
else if (row == item.Key + 1)
{
ICell cellE = iRow.GetCell((int)ExcelColumnEnum.E);
ICell cellQ = iRow.GetCell((int)ExcelColumnEnum.Q);
ICell cellAB = iRow.GetCell((int)ExcelColumnEnum.AD);
//
payment.MKH = (cellE.StringCellValue);
payment.datePrint = (cellQ.ToString());
payment.phoneNVBH = (cellAB.ToString());
}
else if(row == item.Key + 2)
{
ICell cellE = iRow.GetCell((int)ExcelColumnEnum.E);
ICell cellAB = iRow.GetCell((int)ExcelColumnEnum.AB);
//
payment.customerName = (cellE.StringCellValue);
payment.NVGH = (cellAB.StringCellValue);
}
else if(row == item.Key + 3)
{
ICell cellE = iRow.GetCell((int)ExcelColumnEnum.E);
ICell cellAB = iRow.GetCell((int)ExcelColumnEnum.AB);
//
payment.address =(cellE.StringCellValue);
payment.phoneNVGH =(cellAB.StringCellValue);
}
//Console.WriteLine("Số đơn hàng: "+ payment.orderNumber + "| Ngày tạo: "+ payment.dateCreate + "|"+"Nhân viên bảo hành:"+ payment.NVBH);
//increase++;
row++;
}
// Tính Hàng
while (true)
{
IRow r = sheet.GetRow(row);
string check = r?.GetCell((int)ExcelColumnEnum.B).ToString().ToLower();
if (string.IsNullOrEmpty(check))
{
//row++;
}
else
{
if (r.GetCell((int)ExcelColumnEnum.B).ToString().ToLower().Equals("STT".ToLower()))
{
break;
}
}
row++;
}
// Lây dữ liệu từ bảng sản phẩm
row += 1;
//bool isPositionEnd = false;
while(true)
{
IRow iRow = sheet.GetRow(row);
if(iRow.GetCell((int)ExcelColumnEnum.B).ToString().ToUpper().Equals("Khuyến mãi:".ToUpper()))
{
//isPositionEnd = true;
break;
}
else
{
productTable = GetDataToProductTable(iRow);
}
row++;
productTableList.Add(productTable);
}
// Tính ô khuyến mãi
while (true)
{
IRow r = sheet.GetRow(row);
string check = r?.GetCell((int)ExcelColumnEnum.B).ToString().ToLower();
if (string.IsNullOrEmpty(check))
{
//row++;
}
else
{
if (r.GetCell((int)ExcelColumnEnum.B).ToString().ToLower().Equals("STT".ToLower()))
{
break;
}
}
row++;
}
// Lấy dự liệu từ bảng khuyến mãi
row += 1;
bool isPositionEnd = false;
while (!isPositionEnd)
{
PromotionTable promotionTable = new PromotionTable();
for(int i=0; i< 2; i++)
{
IRow iRow = sheet.GetRow(row + i);
if (iRow.GetCell((int)ExcelColumnEnum.B).ToString().ToUpper().Equals("Tổng giá trị đơn hàng:".ToUpper()))
{
isPositionEnd = true;
break;
}
if (i == 0)
{
ICell numericalOrder = iRow.GetCell((int)ExcelColumnEnum.B);
ICell productCode = iRow.GetCell((int)ExcelColumnEnum.C);
ICell name = iRow.GetCell((int)ExcelColumnEnum.F);
ICell HSD = iRow.GetCell((int)ExcelColumnEnum.P);
ICell QC = iRow.GetCell((int)ExcelColumnEnum.W);
ICell bin = iRow.GetCell((int)ExcelColumnEnum.Z);
ICell box = iRow.GetCell((int)ExcelColumnEnum.AC);
ICell infoMoney = iRow.GetCell((int)ExcelColumnEnum.AF);
promotionTable.bin = bin.ToString();
promotionTable.box = box.ToString();
promotionTable.numericalOrder = numericalOrder.ToString();
promotionTable.HSD = HSD.ToString();
promotionTable.productCode = productCode.ToString();
promotionTable.name = name.ToString();
promotionTable.promotionMoney = infoMoney.ToString(); //== "" ? "0" : infoMoney.ToString();
promotionTable.QC = QC.ToString();
}
else if (i == 1)
{
ICell promotionType = iRow.GetCell((int)ExcelColumnEnum.F);
ICell CTKM = iRow.GetCell((int)ExcelColumnEnum.C);
promotionTable.CTKM = CTKM.ToString();
promotionTable.promotionType = promotionType.ToString();
}
}
row += 2;
if (!isPositionEnd)
{
promotionTablesList.Add(promotionTable);
}
}
// Thêm dự liệu vào bảng
payment.productTable = productTableList;
payment.promotionTable = promotionTablesList;
/* allProductTableList.Add(productTableList);
allPromotionTabletList.Add(promotionTablesList);*/
dataList.Add(payment);
}
}
private bool IsClerkName(IRow row,int index)
{
ICell hs0 = row.GetCell(index);
ICell hs1 = row.GetCell(index + 1);
//ICell hs2 = row.GetCell(index + 2);
//ICell hs3 = row.GetCell(index + 3);
if (GetValue(hs0) != string.Empty && hs0 != null && GetValue(hs1) != string.Empty && hs1 != null)
{
return true;
}
return false;
}
protected void Excel()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
string fileName = "";
string[] strFieldsName = { };
string[] strFields = new string[] { };
DataTable dt = null;
BLL.statisticBLL bll = new BLL.statisticBLL();
Dictionary <string, string> dict = getDict();
if (string.IsNullOrEmpty(_group))
{
#region
fileName = "供应商支出分析-明细列表";
dt = bll.getExpendAnalyzeData(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("订单号");
headRow.CreateCell(1).SetCellValue("供应商");
headRow.CreateCell(2).SetCellValue("活动名称");
headRow.CreateCell(3).SetCellValue("活动地点");
headRow.CreateCell(4).SetCellValue("活动结束日期");
headRow.CreateCell(5).SetCellValue("业务性质");
headRow.CreateCell(6).SetCellValue("业务明细");
headRow.CreateCell(7).SetCellValue("应付金额");
headRow.CreateCell(8).SetCellValue("区域");
headRow.CreateCell(9).SetCellValue("业务员");
headRow.CreateCell(10).SetCellValue("地接添加人");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
headRow.GetCell(10).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 15 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_content"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_address"]));
row.CreateCell(4).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-mm-dd"));
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_detail"]));
row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.CreateCell(8).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["op_name"]));
row.CreateCell(10).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_personName"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
row.GetCell(10).CellStyle = cellStyle;
}
}
#endregion
}
else
{
switch (_group)
{
case "1": //供应商
#region
fileName = "供应商支出分析-供应商分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "c_name,c_type,c_business,op_area,na_name", "c_name,c_type,c_business,op_area,na_name", "c_name asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("供应商");
headRow.CreateCell(1).SetCellValue("客户类别");
headRow.CreateCell(2).SetCellValue("业务范围");
headRow.CreateCell(3).SetCellValue("区域");
headRow.CreateCell(4).SetCellValue("业务性质");
headRow.CreateCell(5).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(1).SetCellValue(MettingSys.Common.BusinessDict.customerType()[Utils.ObjToByte(dt.Rows[i]["c_type"])]);
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_business"]));
row.CreateCell(3).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
}
}
#endregion
break;
case "2": //区域
#region
fileName = "供应商支出分析-区域分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "op_area,na_name", "op_area,na_name", "op_area asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("区域");
headRow.CreateCell(1).SetCellValue("业务性质");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(new MettingSys.BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"])));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
case "3": //月份
#region
fileName = "供应商支出分析-月份分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "datepart(year,o_edate) oYear,datepart(month,o_edate) oMonth,na_name", "datepart(year,o_edate),datepart(month,o_edate),na_name", "datepart(year,o_edate) asc,datepart(month,o_edate) asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("月份");
headRow.CreateCell(1).SetCellValue("业务性质");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oYear"]) + "/" + Utils.ObjectToStr(dt.Rows[i]["oMonth"]));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
case "4": //业务性质
#region
fileName = "供应商支出分析-业务性质分组";
dt = bll.getExpendAnalyzeData1(dict, this.pageSize, this.page, "na_name,na_sort,fin_detail", "na_name,na_sort,fin_detail", "na_sort asc", out this.totalCount, out _tFu, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
headRow.CreateCell(0).SetCellValue("业务性质");
headRow.CreateCell(1).SetCellValue("业务明细");
headRow.CreateCell(2).SetCellValue("应付金额");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_detail"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
}
}
#endregion
break;
default:
break;
}
}
//ExcelHelper.Write(HttpContext.Current, dt, fileName, fileName, strFields, strFieldsName, string.Format("{0}.xlsx", fileName));
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
/// <summary>
/// Maps the row to ticket data object.
/// </summary>
/// <param name="row">The IRow object.</param>
/// <returns>ITicketData object.</returns>
private ITicketData MapRowToTicketDataObject(IRow row)
{
try
{
ITicketData data = new TicketData();
data.AgentPT = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.AgentPT)));
data.BetTeam = this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.BetTeam));
data.BetTypeId = Convert.ToInt32(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.BetTypeId)));
data.MasterPT = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.MasterPT)));
data.Odds = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.Odds)));
data.RefNo = this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.RefNo));
data.Stake = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.Stake)));
data.Status = this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.Status));
data.SuperPT = Convert.ToDecimal(this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.SuperPT)));
data.TransDesc = this.GetCellValue(row.GetCell((int)TicketDataTemplateColumnIndex.TransDesc));
return data;
}
catch
{
}
return null;
}
/// <summary>
/// 已存在的excel 转成 table
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="currFilePath">文件路径</param>
/// <returns></returns>
private DataTable ExcelToDataTable(string fileName, string currFilePath)
{
DataTable dt = new DataTable();
IWorkbook wk = null;
FileStream fs = File.OpenRead(currFilePath);
if (fileName.IndexOf(".xlsx") != -1)
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
else
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
fs.Close();
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
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);
dt.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
int startRow = sheet.FirstRowNum + 1;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
return(dt);
}
public int[] FindQuarterColumnIndexes(IRow row)
{
List<int> quarterColumnIndexes = new List<int>();
DateTime curItem;
ICell curCell;
for (int i = 0; i < row.LastCellNum; i++)
{
curCell = row.GetCell(i);
if (curCell != null)
{
if (DateTime.TryParse(curCell.StringCellValue, out curItem))
{
quarterColumnIndexes.Add(i);
}
}
}
if (quarterColumnIndexes.Count == 0)
{
return null;
}
return quarterColumnIndexes.ToArray();
}
private double GetCellSafe(IRow row, Dictionary<string, int> rowName, string name)
{
try
{
double doubletest = 0;
if (rowName[name] > row.LastCellNum)
{
return -1;
}
var cell = row.GetCell(rowName[name]);
if (double.TryParse(cell.ToString(), out doubletest))
{
return doubletest;
}
else
{
return -1;
}
}
catch
{
return -1;
}
}
private Equipment_Chiller SetEquipmentImportData(IRow row)
{
ICell getCell_1 = row.GetCell(1);
ICell getCell_2 = row.GetCell(2);
ICell getCell_3 = row.GetCell(3);
ICell getCell_4 = row.GetCell(4);
if (getCell_1 != null && getCell_2 != null && getCell_3 != null && getCell_4 != null)
{
string value_1 = getCell_1.StringCellValue;
decimal value_2 = (decimal)getCell_2.NumericCellValue;
decimal value_3 = (decimal)getCell_3.NumericCellValue;
string value_4 = getCell_4.StringCellValue;
if (!string.IsNullOrEmpty(value_1)
&& value_2 > 0
&& value_3 > 0
&& !string.IsNullOrEmpty(value_4)
)
{
bool getIsNews = false;
if (dicEquipmentIsNew.ContainsKey(value_4))
{
getIsNews = dicEquipmentIsNew[value_4];
}
return new Equipment_Chiller()
{
equipment_chiller_id = GetNewId(ProcCore.Business.CodeTable.Equipment_Chiller),
equipment_sn = value_1,
td_evaporate_set = value_2,
td_condenser_set = value_3,
is_new_equip = getIsNews,
i_InsertDateTime = DateTime.Now,
USERID = this.UserId,
};
}
else
{
return null;
}
}
else
{
return null;
}
}
double getCellDouble(int no, IRow row)
{
if (no < 0) return 0;
ICell cell = row.GetCell(no, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (cell.CellType == CellType.Numeric)
{
return cell.NumericCellValue;
}
return 0;
}
public static String getCellValue(IRow row, int cell_index)
{
ICell cell = row.GetCell(cell_index);
String result = "";
if (cell == null)
return result;
CellType ctype = cell.CellType;
if (ctype == CellType.Blank)//取得空白
result = "";
else if (ctype == CellType.String)//字串
result = cell.StringCellValue.ToString();
else if (ctype == CellType.Numeric)//數字
result = cell.NumericCellValue.ToString();
else if (ctype == CellType.Boolean)//布林
result = cell.BooleanCellValue.ToString();
else
result = "";
return result;
}
/// <summary>
/// 将Excel文件流导入到List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileStream"></param>
/// <param name="options"></param>
/// <returns></returns>
public List <T> ImportExcel <T>(Stream fileStream, ExcelOptions options) where T : new()
{
if (options == null)
{
options = new ExcelOptions();
}
if (options.StartRowIndex < 0)
{
options.StartRowIndex = 0;
}
if (options.StartColumnIndex < 0)
{
options.StartColumnIndex = 0;
}
//创建Excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
ISheet sheet = null;
if (!string.IsNullOrEmpty(options.SheetName))
{
sheet = workbook.GetSheet(options.SheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
//实例化T数组
List <T> list = new List <T>();
if (sheet != null)
{
//一行最后一个cell的编号 即总的列数
IRow cellNum = sheet.GetRow(0);
int num = cellNum.LastCellNum;
//获取泛型对象T的所有属性
var propertys = typeof(T).GetProperties();
//每行转换为单个T对象
for (int i = options.StartRowIndex; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
var obj = new T();
var pi = 0;
for (int j = options.StartColumnIndex; j < num; j++)
{
//没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
var value = row.GetCell(j).ToString();
string str = (propertys[pi].PropertyType).FullName;
if (str == "System.String")
{
propertys[pi].SetValue(obj, value, null);
}
else if (str == "System.DateTime")
{
DateTime pdt = Convert.ToDateTime(value);
propertys[pi].SetValue(obj, pdt, null);
}
else if (str == "System.Boolean")
{
bool pb = Convert.ToBoolean(value);
propertys[pi].SetValue(obj, pb, null);
}
else if (str == "System.Int16")
{
short pi16 = Convert.ToInt16(value);
propertys[pi].SetValue(obj, pi16, null);
}
else if (str == "System.Int32")
{
int pi32 = Convert.ToInt32(value);
propertys[pi].SetValue(obj, pi32, null);
}
else if (str == "System.Int64")
{
long pi64 = Convert.ToInt64(value);
propertys[pi].SetValue(obj, pi64, null);
}
else if (str == "System.Byte")
{
byte pb = Convert.ToByte(value);
propertys[pi].SetValue(obj, pb, null);
}
else if (str == "System.Decimal")
{
var d = Convert.ToDecimal(value);
propertys[pi].SetValue(obj, d, null);
}
else
{
propertys[pi].SetValue(obj, null, null);
}
}
pi++;
}
list.Add(obj);
}
}
return(list);
}
/**
* @return <code>null</code> if cell is missing, empty or blank
*/
private static String GetCellTextValue(IRow r, int colIndex, String columnName)
{
if (r == null)
{
return null;
}
ICell cell = r.GetCell(colIndex);
if (cell == null)
{
return null;
}
if (cell.CellType == CellType.BLANK)
{
return null;
}
if (cell.CellType == CellType.STRING)
{
return cell.RichStringCellValue.String;
}
throw new RuntimeException("Bad cell type for '" + columnName + "' column: ("
+ cell.CellType + ") row (" + (r.RowNum + 1) + ")");
}
public static ICell CopyCell(IRow row, int sourceIndex, int targetIndex)
{
if (sourceIndex == targetIndex)
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
// Grab a copy of the old/new cell
ICell oldCell = row.GetCell(sourceIndex);
// If the old cell is null jump to next cell
if (oldCell == null)
{
return null;
}
ICell newCell = row.GetCell(targetIndex);
if (newCell == null) //not exist
{
newCell = row.CreateCell(targetIndex);
}
else
{
//TODO:shift cells
}
// Copy style from old cell and apply to new cell
if (oldCell.CellStyle != null)
{
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
return newCell;
}
private void setCellStyle(IRow irow, int cellno, ICellStyle ics)
{
for (int i = 0; i < cellno; i++)
{
irow.GetCell(i).CellStyle = ics;
}
}
/// <summary>
/// A Row is parable and this method will return true only if:
/// - it does not contain a field title
/// - it does not contain metadata
/// - it does not contain a variable
/// - it is not empty
/// </summary>
/// <param name="row">row to check</param>
/// <returns>true if row is parsable</returns>
private bool RowIsParsable(IRow row)
{
if (row == null || row.Cells.Count == 0) {
return false;
}
bool isEmpty = true;
for (var i = row.FirstCellNum; i < row.LastCellNum; ++i) {
ICell cell = row.GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell != null) {
string val = CellValueAsString(cell);
if (val[0] == '[' || val[0] == '#' || val[0] == '$') {
return false;
} else if (val != "") {
isEmpty = false;
}
}
}
if (isEmpty) {
return false;
}
return true;
}
string getCellString(int no, IRow row)
{
if (no < 0) return "";
ICell cell = row.GetCell(no, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (cell.CellType == CellType.Numeric)
{
return cell.NumericCellValue.ToString().Replace("/b", "");
}
else if (cell.CellType == CellType.String)
{
return cell.StringCellValue.Trim();
}
return "";
}
public CaracteristiqueImmeuble(IRow row)
{
CodeImmeuble = row.GetCell(0).ToString();
Rue = row.GetCell(1).ToString();
NumeroRue = row.GetCell(2).ToString();
}
private static string GetCellString(IRow row, int i)
{
return(row?.GetCell(i)?.ToString() ?? "");
}
private Supplier_VendorInfo SingleExcelToList(ISheet sheet, int startRow, ref string msg)
{
Supplier_VendorInfo vendorInfo = new Supplier_VendorInfo();
if (sheet == null || sheet.LastRowNum < 64)
{
msg = "Excel内容为空！";
return(null);
}
//第一行为标题
try
{
string code = sheet.GetRow(5)?.GetCell(9)?.ToString().Replace("供应商代码：", "").Trim() ?? "";
if (string.IsNullOrWhiteSpace(code))
{
msg = "供应商代码不能为空";
return(null);
}
//供应商代码如果已存在，就跳过
var models = Bll.BllSupplier_List.First(o => o.Code == code && o.Display != 2);
if (models != null)
{
msg = "供应商代码已存在";
return(null);
}
#region 填充数据
vendorInfo.Code = code;
vendorInfo.TableNumber = sheet.GetRow(1)?.GetCell(9)?.ToString().Replace("表格编号：", "").Trim() ?? "";
IRow row = sheet.GetRow(2);
vendorInfo.Owners = row?.GetCell(0)?.ToString().Replace("所有者Owners:", "").Trim() ?? "";
vendorInfo.Version = row?.GetCell(9)?.ToString().Replace("版本号：", "").Trim() ?? "";
vendorInfo.EffectiveDate = sheet.GetRow(3)?.GetCell(9)?.ToString().Replace("生效日期：", "").Trim().ToDateTime() ?? null;
vendorInfo.VersionType = Tools.Enums.Tools.GetValueByName(typeof(GetVersionTypeId), sheet.GetRow(6)?.GetCell(0)?.ToString().Trim());
vendorInfo.WriteDate = sheet.GetRow(7)?.GetCell(1)?.ToString().Replace("Date （日期）：", "").Trim().ToDateTime() ?? null;
vendorInfo.Name = sheet.GetRow(10)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.CompanyEnglishName = sheet.GetRow(11)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.AddressOfRegisteredOffice = sheet.GetRow(12)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.PostCode1 = sheet.GetRow(13)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.PurchaseOrderAddressedTo = sheet.GetRow(14)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.PostCode2 = sheet.GetRow(15)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.MainContactor = sheet.GetRow(16)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.TelephoneNumber = sheet.GetRow(17)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.FaxNumber = sheet.GetRow(18)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.EmailForPO = sheet.GetRow(19)?.GetCell(7)?.ToString().Trim() ?? "";
row = sheet.GetRow(20);
vendorInfo.FinanceContact = row?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.FinanceContactPhone = row?.GetCell(8)?.ToString().Trim() ?? "";
vendorInfo.FinanceContactEmail = row?.GetCell(9)?.ToString().Trim() ?? "";
vendorInfo.TaxNumber = sheet.GetRow(21)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.TaxRate = sheet.GetRow(22)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.OrderCurrency = sheet.GetRow(23)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.PaymentTerms = sheet.GetRow(24)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.Incoterms = sheet.GetRow(25)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.BankKey = sheet.GetRow(26)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.SwiftCode = sheet.GetRow(27)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.NameOfBank = sheet.GetRow(28)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.ACNo = sheet.GetRow(29)?.GetCell(4)?.ToString().Trim() ?? "";
vendorInfo.FormOfBusiness = Tools.Enums.Tools.GetValueByName(typeof(GetFormOfBusinessId), sheet.GetRow(31)?.GetCell(1)?.ToString().Trim());
vendorInfo.BusinessRegistrationNumber = sheet.GetRow(32)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.RegisteredCapital = sheet.GetRow(33)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.CertificateOfCorporation = sheet.GetRow(34)?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.CompanyWebsiteAddress = sheet.GetRow(35)?.GetCell(7)?.ToString().Trim() ?? "";
row = sheet.GetRow(36);
string[] arr = new string[3];
arr = row?.GetCell(7)?.ToString().Trim().Split('/') ?? new string[3];
vendorInfo.ProductsOrServiceSales = arr[0]?.Trim() ?? "";
vendorInfo.MOQ = arr[1]?.Trim().ToInt32() ?? 0;
vendorInfo.LeadTime = arr[2]?.Trim() ?? "";
row = sheet.GetRow(38);
vendorInfo.ManagementMenberName1 = row?.GetCell(5)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberName2 = row?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberName3 = row?.GetCell(8)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberName4 = row?.GetCell(9)?.ToString().Trim() ?? "";
row = sheet.GetRow(39);
vendorInfo.ManagementMenberTitle1 = row?.GetCell(5)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberTitle2 = row?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberTitle3 = row?.GetCell(8)?.ToString().Trim() ?? "";
vendorInfo.ManagementMenberTitle4 = row?.GetCell(9)?.ToString().Trim() ?? "";
row = sheet.GetRow(42);
vendorInfo.ContactPersonsName1 = row?.GetCell(1)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsTitle1 = row?.GetCell(5)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsTel1 = row?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsFax1 = row?.GetCell(8)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsEmail1 = row?.GetCell(9)?.ToString().Trim() ?? "";
row = sheet.GetRow(43);
vendorInfo.ContactPersonsName2 = row?.GetCell(1)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsTitle2 = row?.GetCell(5)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsTel2 = row?.GetCell(7)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsFax2 = row?.GetCell(8)?.ToString().Trim() ?? "";
vendorInfo.ContactPersonsEmail2 = row?.GetCell(9)?.ToString().Trim() ?? "";
vendorInfo.AddDate = DateTime.Now;
vendorInfo.AddUserId = MyInfo.Id;
vendorInfo.LastDate = vendorInfo.AddDate;
vendorInfo.LastUserId = vendorInfo.AddUserId;
vendorInfo.Dispaly = 1;
#endregion
}
catch (Exception)
{
msg = "请联系管理员";
return(null);
}
return(vendorInfo);
}
