/**
* 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 or -1 if cell is empty
*/
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);
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;
//AttributedString str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
windowsFont = IFont2Font(font);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
}
}
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);
width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell);
}
}
}
return(width);
}
protected bool IsTextEmpty(ICell cell)
{
string value;
switch (cell.CellType)
{
case CellType.String:
// XXX: enrich
value = cell.RichStringCellValue.String;
break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.String:
IRichTextString str = cell.RichStringCellValue as IRichTextString;
if (str == null || str.Length <= 0)
{
return(false);
}
value = str.ToString();
break;
case CellType.Numeric:
ICellStyle style = cell.CellStyle as ICellStyle;
if (style == null)
{
return(false);
}
value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
value = string.Empty;
break;
}
break;
case CellType.Blank:
value = string.Empty;
break;
case CellType.Numeric:
value = _formatter.FormatCellValue(cell);
break;
case CellType.Boolean:
value = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
return(true);
}
return(string.IsNullOrEmpty(value));
}
private async Task ResumeAfterPrompt(IDialogContext context, IAwaitable <IEnumerable <Attachment> > result)
{
context.UserData.SetValue(ContextConstants.UserId, context.Activity.From.Id);
try
{
//Connecting to storage
var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["AzureWebJobsStorage"]);
var tableClient = storageAccount.CreateCloudTableClient();
CloudTable eventTable = tableClient.GetTableReference("Event");
eventTable.CreateIfNotExists();
//Get the Attachment
var message = await result;
var msg = context.MakeMessage();
msg.Type = ActivityTypes.Typing;
await context.PostAsync(msg);
var excelName = "";
string destinationContainer = "surveytemplates";
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
var blobContainer = blobClient.GetContainerReference(destinationContainer);
blobContainer.CreateIfNotExists();
String newFileName = "";
//Receiving the attachment
foreach (Attachment a in message)
{
try
{
excelName = a.Name;
using (HttpClient httpClient = new HttpClient())
{
var responseMessage = await httpClient.GetAsync(a.ContentUrl);
var contentLenghtBytes = responseMessage.Content.Headers.ContentLength;
var fileByte = await httpClient.GetByteArrayAsync(a.ContentUrl);
//Set file name
newFileName = context.UserData.GetValue <string>(ContextConstants.UserId) + "_" + DateTime.Now.ToString("MMddyyyy-hhmmss") + "_" + excelName;
string sourceUrl = a.ContentUrl;
// Set the permissions so the blobs are public. //ON HOLD
BlobContainerPermissions permissions = new BlobContainerPermissions
{
PublicAccess = BlobContainerPublicAccessType.Blob
};
blobContainer.SetPermissions(permissions);
var newBlockBlob = blobContainer.GetBlockBlobReference(newFileName);
try
{
using (var memoryStream = new System.IO.MemoryStream(fileByte))
{
newBlockBlob.UploadFromStream(memoryStream);
newBlockBlob.Properties.ContentType = a.ContentType;
}
newBlockBlob.SetProperties();
}
catch (Exception ex)
{
await context.PostAsync("Something went wrong with uploading, please type \'/ce\' to try again.");
context.Done(this);
}
}
}
catch (Exception e)
{
await context.PostAsync("Something went wrong with uploading, please type \'/ce\' to try again.");
context.Done(this);
}
}
try
{
//Opening the Excel File
CloudBlockBlob blob = blobContainer.GetBlockBlobReference(newFileName);
//Check for unique entity
DateTime thisDay = DateTime.Today;
String today = FormatDate(thisDay.ToString("d"));
var eventCode = GenerateCode();
//After checking that there are no duplicates in generated codes
EventEntity eventEntity = new EventEntity(context.UserData.GetValue <string>(ContextConstants.UserId), eventCode);
using (HttpClient httpClient = new HttpClient())
{
var fileByte = await httpClient.GetByteArrayAsync(blob.Uri.ToString());
try
{
XSSFWorkbook wb;
XSSFSheet sh;
using (var memoryStream = new MemoryStream(fileByte))
{
wb = new XSSFWorkbook(memoryStream);
}
sh = (XSSFSheet)wb.GetSheetAt(0);
//Check event start date and event name
if (sh.GetRow(2).GetCell(1) != null && sh.GetRow(2).GetCell(1).ToString() != "" && sh.GetRow(1).GetCell(1) != null && sh.GetRow(1).GetCell(1).ToString() != "")
{
//DateTime dateTime;
DataFormatter dataFormatter = new DataFormatter();
var date = dataFormatter.FormatCellValue(sh.GetRow(2).GetCell(1));
int totalDays = 1;
var error = false;
DateTime sDate;
DateTime eDate;
//Try to parse date in the specified format
if (ParseDate(date, thisDay))
{
String[] split = date.Split(new Char[] { '/', '-' });
date = split[0].ToString() + '/' + split[1].ToString() + '/' + split[2];
eventEntity.EventStartDate = date;
DateTime.TryParseExact(dataFormatter.FormatCellValue(sh.GetRow(2).GetCell(1)), new string[] { "dd/MM/yyyy", "dd-MM-yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out sDate);
//Check Event End Date
if (sh.GetRow(3).GetCell(1) != null && sh.GetRow(3).GetCell(1).ToString() != "")
{
date = dataFormatter.FormatCellValue(sh.GetRow(3).GetCell(1));
if (ParseDate(date, sDate))
{
split = date.Split(new Char[] { '/', '-' });
date = split[0].ToString() + '/' + split[1].ToString() + '/' + split[2];
eventEntity.EventEndDate = date;
DateTime.TryParseExact(dataFormatter.FormatCellValue(sh.GetRow(3).GetCell(1)), new string[] { "dd/MM/yyyy", "dd-MM-yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out eDate);
totalDays = (eDate - sDate).Days + 1;
if (totalDays > 31)
{
error = true;
await context.PostAsync("Sorry, we currently do not support events that have more than 31 days.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
error = true;
await context.PostAsync("Please check your excel file and input an end date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
eventEntity.EventEndDate = eventEntity.EventStartDate;
}
//End of Checking Event's End Date
//Check Attendance Code 1 Time
var startTime = dataFormatter.FormatCellValue(sh.GetRow(4).GetCell(1));
var endTime = dataFormatter.FormatCellValue(sh.GetRow(5).GetCell(1));
var code1 = new List <string>();
var code2 = new List <string>();
if (sh.GetRow(4).GetCell(1) != null && sh.GetRow(4).GetCell(1).ToString() != "" && sh.GetRow(5).GetCell(1) != null && sh.GetRow(5).GetCell(1).ToString() != "")
{
if (ParseTime(startTime, endTime))
{
eventEntity.AttendanceCode1StartTime = startTime;
eventEntity.AttendanceCode1EndTime = endTime;
for (int i = 0; i < totalDays; i++)
{
//Generate unique attendanceCode1
code1.Add(GenerateCode());
}
}
else
{
error = true;
await context.PostAsync("Please check your excel file and valid Attendance Code 1 Start/End Time in the format specified(2359). The end time should not be before the start time.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else if (sh.GetRow(4).GetCell(1).ToString() == "" && sh.GetRow(5).GetCell(1).ToString() == "")
{
eventEntity.AttendanceCode1StartTime = "";
eventEntity.AttendanceCode1EndTime = "";
}
else
{
error = true;
await context.PostAsync("Please check your excel file and valid Attendance Code 1 Start/End Time in the format specified(2359). The end time should not be before the start time.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
//End of Attendance Code 1 Time
//Check Attendance Code 2 Time
startTime = dataFormatter.FormatCellValue(sh.GetRow(6).GetCell(1));
endTime = dataFormatter.FormatCellValue(sh.GetRow(7).GetCell(1));
if (sh.GetRow(6).GetCell(1) != null && sh.GetRow(6).GetCell(1).ToString() != "" && sh.GetRow(7).GetCell(1) != null && sh.GetRow(7).GetCell(1).ToString() != "")
{
if (ParseTime(startTime, endTime))
{
eventEntity.AttendanceCode2StartTime = startTime;
eventEntity.AttendanceCode2EndTime = endTime;
for (int i = 0; i < totalDays; i++)
{
//Generate unique attendanceCode2
code2.Add(GenerateCode());
}
}
else
{
error = true;
await context.PostAsync("Please check your excel file and valid Attendance Code 2 Start/End Time in the format specified(2359). The end time should not be before the start time.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
eventEntity.AttendanceCode2StartTime = "";
eventEntity.AttendanceCode2EndTime = "";
}
//End of Attendance Code 2 Time
//Check Survey End Date
if (sh.GetRow(8).GetCell(1) != null && sh.GetRow(8).GetCell(1).ToString() != "")
{
date = dataFormatter.FormatCellValue(sh.GetRow(8).GetCell(1));
if (ParseDate(date, sDate))
{
split = date.Split(new Char[] { '/', '-' });
date = split[0] + '/' + split[1] + '/' + split[2];
eventEntity.SurveyEndDate = date;
}
else//Input date is not in the specified format
{
error = true;
await context.PostAsync("Please check your excel file and input a Survey End Date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
if (eventEntity.EventEndDate != "")
{
eventEntity.SurveyEndDate = eventEntity.EventEndDate;
}
else
{
eventEntity.SurveyEndDate = eventEntity.EventStartDate;
}
}
//End of checking Survey End Date
//Check Survey End Time
endTime = dataFormatter.FormatCellValue(sh.GetRow(9).GetCell(1));
if (sh.GetRow(9).GetCell(1) != null && sh.GetRow(9).GetCell(1).ToString() != "")
{
if (ParseTime("0000", endTime))
{
eventEntity.SurveyEndTime = endTime;
}
else
{
error = true;
await context.PostAsync("Please check your excel file and your Survey End Time in the format specified(2359).");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
eventEntity.SurveyEndTime = "2359";
}
//End of Attendance Code 2 Time
//Start of Anonymity
if (sh.GetRow(11).GetCell(1) != null && sh.GetRow(11).GetCell(1).ToString() != "" && (sh.GetRow(11).GetCell(1).ToString() == "Y" || sh.GetRow(11).GetCell(1).ToString() == "N" || sh.GetRow(11).GetCell(1).ToString() == "Yes" || sh.GetRow(11).GetCell(1).ToString() == "No"))
{
eventEntity.Anonymous = (sh.GetRow(11).GetCell(1).ToString());
}
else
{
error = true;
await context.PostAsync("Please check your excel file and input either Y or N for Anonymity.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
//End of Anonymity
//Start of Email
if (sh.GetRow(12).GetCell(1) != null && sh.GetRow(12).GetCell(1).ToString() != "")
{
try
{
System.Net.Mail.MailAddress mail = new System.Net.Mail.MailAddress(sh.GetRow(12).GetCell(1).ToString());
eventEntity.Email = (sh.GetRow(12).GetCell(1).ToString());
}
catch (FormatException)
{
error = true;
await context.PostAsync("Please check your excel file and input a valid Email address or remove the field.");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
eventEntity.Email = "";
}
//End of Email
//If the file got no error parsing all the values
if (!error)
{
//Setting eventEntity variables
eventEntity.Description = dataFormatter.FormatCellValue(sh.GetRow(10).GetCell(1));
eventEntity.EventName = (sh.GetRow(1).GetCell(1).ToString());
if (sh.GetRow(13).GetCell(1).ToString() != "")
{
eventEntity.Password = Sha256(sh.GetRow(13).GetCell(1).ToString());
}
else
{
eventEntity.Password = "";
}
var survey = new List <QuestionEntity>();
QuestionEntity question = new QuestionEntity();
eventEntity.SurveyCode = GenerateCode();
//retrieving the questions
for (int i = 35; i <= sh.LastRowNum; i++)
{
var answerList = new List <string>();
if (sh.GetRow(i).GetCell(0) != null && sh.GetRow(i).GetCell(0).ToString() != "")
{
int x = 1;
question = new QuestionEntity(eventEntity.SurveyCode, (i - 34).ToString());
question.QuestionText = (sh.GetRow(i).GetCell(0).ToString());
//Retrieving the answer list
while (sh.GetRow(i).GetCell(x) != null && sh.GetRow(i).GetCell(x).ToString() != "")
{
answerList.Add(sh.GetRow(i).GetCell(x).ToString());
x++;
}
}
else
{
break;
}
question.AnswerList = JsonConvert.SerializeObject(answerList);
survey.Add(question);
}
//No question is found in excel file
if (survey.Count == 0)
{
await context.PostAsync("Your excel file does not contain any question, please check again! (Read the guidelines in the provided template)");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
else//Questions were found in excel file, process inserting of event
{
eventEntity.Survey = JsonConvert.SerializeObject(survey);
for (int i = 0; i < totalDays; i++)
{
if (code1.Count != 0)
{
eventEntity.AttendanceCode1 = code1[i];
}
if (code2.Count != 0)
{
eventEntity.AttendanceCode2 = code2[i];
}
string code = "";
while (true)
{
code = Guid.NewGuid().ToString().Substring(0, 8);
TableOperation retrieveOperation = TableOperation.Retrieve <EventEntity>(context.UserData.GetValue <string>(ContextConstants.UserId), code);
TableResult retrievedResult = eventTable.Execute(retrieveOperation);
if (retrievedResult.Result == null)
{
break;
}
}
eventEntity.RowKey = code;
eventEntity.Day = (i + 1).ToString();
TableOperation insertOperation1 = TableOperation.InsertOrMerge(eventEntity);
eventTable.Execute(insertOperation1);
}
await context.PostAsync(msg);
string eventMsg = "'" + eventEntity.EventName + "' has been created, with a total of " + survey.Count + " questions. \n\n";
eventMsg += "Date: " + eventEntity.EventStartDate + " to " + eventEntity.EventEndDate + "\n\n";
eventMsg += "Attendance Code 1: \n\n";
for (int i = 0; i < code1.Count; i++)
{
eventMsg += "Day " + (i + 1) + ": " + (code1[i] == null ? "-" : code1[i]) + "\n\n";
}
eventMsg += "\n\n" + "Attendance Code 2: \n\n";
for (int i = 0; i < code2.Count; i++)
{
eventMsg += "Day " + (i + 1) + ": " + (code2[i] == null ? "-" : code2[i]) + "\n\n";
}
eventMsg += "\n\n" + "Survey Code: " + eventEntity.SurveyCode + "\n\n\n\n"
+ "Type /Event to manage your event. Thank you and have a great day ahead!";
await context.PostAsync(eventMsg);
bool a = SharedFunction.EmailEvent(eventEntity, context.UserData.GetValue <string>(ContextConstants.Name), survey, code1, code2);
context.Done(this);
}
}
}
else//Input date is not in the specified format
{
await context.PostAsync("Please check your excel file and input a date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
else
{
await context.PostAsync("Please check your excel file to see if you have inputted an Event Name and Event Start Date!");
await context.PostAsync(msg);
await context.PostAsync("Talk to me again if you require my assistance.");
context.Done(this);
}
}
catch (Exception ex)
{
await context.PostAsync("Sorry there is an error trying to open your file, please type \'/ce\' to try again.");
context.Done(this);
}
}
}
catch (Exception e) {
await context.PostAsync("Sorry there is an error trying to open your file, please type \'/ce\' to try again.");
context.Done(this);
}
}
catch (TooManyAttemptsException e)
{
}
}
public List <string> GetListDataFromFile(string pathFile, string fileName, string importer)
{
FileStream fs = new FileStream(pathFile, FileMode.Open);
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(fs);
// Get first/desired sheet from the workbook
ISheet sheet = workbook.GetSheetAt(0);
// Prepare variable
List <string> result = new List <string>();
DataFormatter formatter = new DataFormatter();
JObject jsonObject = new JObject();
JObject jsonObjectTemp = null;
JObject finalObj = null;
List <CellRangeAddress> listMergedRegion = new List <CellRangeAddress>();
int maxMergedRegionRowLength = GetMaxMergedRegionRowLength(sheet, listMergedRegion);
ICell parentCell = null;
string parentStringCellValue = string.Empty;
string currStringCellValue = string.Empty;
int currRowIndex = 0;
int currColIndex = 0;
bool isReadHeader = true;
string jsonBluePrint = string.Empty; // Use to set value by key when read data after read header
SortMergedRegions(listMergedRegion, true);
SortMergedRegions(listMergedRegion, false);
AddMergeRegionToJSON(sheet, maxMergedRegionRowLength, listMergedRegion, jsonObject, 0, string.Empty);
// Start reading
int lastRowNum = sheet.LastRowNum;
for (int i = 0; i <= lastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
// For each row, iterate through all the columns
List <ICell> cells = row.Cells;
if (!string.Empty.Equals(jsonBluePrint))
{
jsonObjectTemp = JObject.Parse(jsonBluePrint);
}
foreach (ICell cell in cells)
{
if (isReadHeader)
{
if (cell.CellType != CellType.Blank)
{
// Reading header -> ICell always string
currStringCellValue = cell.StringCellValue;
if ("(1)".Equals(currStringCellValue))
{
isReadHeader = false;
finalObj = new JObject {
{ Keywords.NAME_SURVEY, fileName },
{ Keywords.IMPORTER, importer },
{ Keywords.CREATED_AT, DateTime.Now.ToString(Configs.DATETIME_FORMAT, CultureInfo.InvariantCulture) },
{ Keywords.DATA, jsonObject }
};
jsonBluePrint = JsonConvert.SerializeObject(finalObj);
break;
}
currRowIndex = cell.RowIndex;
currColIndex = cell.ColumnIndex;
if (currRowIndex < _numberAddedRow)
{
// These cell in row was added
continue;
}
if (currRowIndex != 0)
{
parentCell = GetParentCell(sheet, currRowIndex, currColIndex);
}
if (!jsonObject.ContainsKey(currStringCellValue))
{
if (parentCell != null && parentCell.CellType != CellType.Blank)
{
parentStringCellValue = parentCell.StringCellValue;
}
AddKeyToJsonObject(jsonObject, parentStringCellValue, currStringCellValue, false);
}
}
}
else
{
SetValueToJSONBluePrint(jsonObjectTemp, formatter.FormatCellValue(cell), 0);
}
}
if (jsonObjectTemp != null)
{
result.Add(JsonConvert.SerializeObject(jsonObjectTemp));
}
}
fs.Close();
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;
// 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);
Font windowsFont = IFont2Font(font);
//AttributedString str;
//TextLayout layout;
double width = -1;
using (Graphics g = Graphics.FromHwnd(IntPtr.Zero))
{
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());
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 = MeasureString(g, 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) + 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(MeasureString(g, txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) + 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());
//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 = MeasureString(g, 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) + cell.CellStyle.Indention);
}
else
{
//width = Math.max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(MeasureString(g, txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) + cell.CellStyle.Indention);
}
}
}
}
return(width);
}
//取出excel資料
public ChartData ExtractFile(List <FileDetail> details, string connId)
{
ChartData chartData = new ChartData();
string fileName = "";
if (details != null)
{
CalculateFileSize(details);
var task = Task.Factory.StartNew(() =>
{
foreach (var detail in details)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
chartData.Type = "LinkChart";
ImportHelper.KeyLineNodeID = 1;
fileName = detail.OriginFileName;
DataFormatter format = new DataFormatter(CultureInfo.CurrentCulture);
try
{
using (fs = File.OpenRead(detail.PhysicalPath))
{
if (detail.Extend == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
///舊excel
if (detail.Extend == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
if (sheet != null && sheet.LastRowNum > 0)
{
//找first row，並依照關鍵字ImportHelper.FileTypeList() 找資料類型
FindRow(sheet, detail);
ImportHelper.Count += detail.StartRow;
for (int i = detail.StartRow; i <= sheet.LastRowNum; i++)
{
NodePair pair = null;
ImportHelper.Count++;
IRow row = sheet.GetRow(i);
string node1ID, node2ID, linkTxt;
switch (detail.FileType)
{
case "電話通聯":
node1ID = format.FormatCellValue(row.GetCell(1));
node2ID = format.FormatCellValue(row.GetCell(2));
pair = MapNodePair(node1ID, node2ID);
pair.Node1.Data.Category = "phone";
pair.Node2.Data.Category = "phone";
pair.Link.Text = "1";
pair.Link.Arrow2 = true;
pair.Link.Data.LinkDatas.Add(new LinkData()
{
DateTime = format.FormatCellValue(row.GetCell(3)),
Period = format.FormatCellValue(row.GetCell(4)),
ComType = format.FormatCellValue(row.GetCell(0)),
IMEI = format.FormatCellValue(row.GetCell(5)),
BaseStation = format.FormatCellValue(row.GetCell(7))
});
//存到資料庫
if (detail.ImportDB)
{
SaveToDatabase(sheet.GetRow(i));
}
break;
case "成員名冊":
node1ID = format.FormatCellValue(row.GetCell(0));
node2ID = format.FormatCellValue(row.GetCell(1));
pair = MapNodePair(node1ID, node2ID);
pair.Link.Text = "";
break;
default:
break;
}
if (pair != null)
{
AddNodeLink(chartData, pair);
}
}
if (detail.ImportDB)
{
recRep.SaveChanges();
}
}
}
}
}
catch (Exception e)
{
if (fs != null)
{
fs.Close();
}
}
}
});
float ratio = 0;
Action UpdateProgress = () =>
{
if (ImportHelper.Total != 0)
{
ratio = (float)((double)ImportHelper.Count / (double)ImportHelper.Total);
FileUploaderHub.UpdateProcess(connId, fileName, ratio * 100);
}
};
while (!task.IsCompleted && !task.IsCanceled && !task.IsFaulted)
{
UpdateProgress();
Thread.Sleep(200);
}
UpdateProgress();
if (task.IsFaulted)
{
FileUploaderHub.UpdateProcess(connId, "-", 0, string.Join("|", task.Exception.InnerExceptions.Select(o => o.Message).ToArray()));
}
}
//FileUploaderHub.UpdateProcess(connId, "HAHA", 0, "-");
return(chartData);
}
public TreeNodeCollection Parse(string filePath)
{
var treeView = new TreeView();
IWorkbook workbook = null;
var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
var formatter = new DataFormatter();
var cellsList = new List <Cell>();
var sheetsNumber = workbook.NumberOfSheets;
sheetsNumber = 2;
var linesList = new List <Line>();
for (var sheetIndex = 0; sheetIndex < sheetsNumber; sheetIndex++)
{
var sheet = workbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
_treeRowsList = new List <TreeNode>();
var rowCount = sheet.LastRowNum; // This may not be valid row count.
// If first row is table head, i starts from 1
for (var rowNum = 0; rowNum < rowCount; rowNum++)
{
_treeCellsList = new List <TreeNode>();
var curRow = sheet.GetRow(rowNum);
if (curRow == null)
{
rowCount = rowNum - 1;
break;
}
var elementsList = new List <Element>();
foreach (var cell in curRow.Cells)
{
if (cell != null)
{
// 1. AddCell
var cellFont = cell.CellStyle.GetFont(workbook);
var cellObject = new Cell
{
column = cell.ColumnIndex,
row = cell.RowIndex,
sheet = sheetIndex,
style = new Style
{
alignment = cell.CellStyle.Alignment,
backgroundColor = null,
font = cellFont.FontName,
fontSize = cellFont.FontHeight,
foregroundColor = null,
isBold = cellFont.IsBold,
isItalic = cellFont.IsItalic,
isUnderline = cellFont.Underline != null ? true : false,
isStrikeout = cellFont.IsStrikeout,
verticalAlignment = cell.CellStyle.VerticalAlignment
}
};
//var cellNodesList = new List<TreeNode>();
//cellNodesList.Add(new TreeNode(nameof(cell.RowIndex) + " = " + cell.RowIndex));
//cellNodesList.Add(new TreeNode(nameof(cell.ColumnIndex) + " = " + cell.ColumnIndex));
//cellNodesList.Add(
// new TreeNode(nameof(cell.CellStyle.Alignment) + " = " + cell.CellStyle.Alignment));
//cellNodesList.Add(new TreeNode(nameof(cell.IsMergedCell) + " = " + cell.IsMergedCell));
//cellNodesList.Add(new TreeNode(nameof(cellFont.FontName) + " = " + cellFont.FontName));
//cellNodesList.Add(new TreeNode(nameof(cellFont.Color) + " = " + cellFont.Color));
//cellNodesList.Add(new TreeNode(nameof(cellFont.IsBold) + " = " + cellFont.IsBold));
//cellNodesList.Add(new TreeNode(nameof(cellFont.IsItalic) + " = " + cellFont.IsItalic));
//cellNodesList.Add(
// new TreeNode(nameof(cellFont.FontHeight) + " = " + cellFont.FontHeight));
var formatCellValue = formatter.FormatCellValue(cell);
//cellNodesList.Add(new TreeNode(
// nameof(formatCellValue) + " = " + formatCellValue));
cellObject.displayContent = formatCellValue;
string cellContent = null;
if (cell.CellType == CellType.String)
{
//cellNodesList.Add(new TreeNode(
// nameof(cell.RichStringCellValue) + " = " + cell.RichStringCellValue));
//cellNodesList.Add(
// new TreeNode(nameof(cell.StringCellValue) + " = " + cell.StringCellValue));
cellContent = cell.RichStringCellValue.ToString();
cellObject.cellType = Model.CellType.String;
}
else if (cell.CellType == CellType.Numeric)
{
var myCell = cell;
//cellNodesList.Add(new TreeNode(
// nameof(cell.NumericCellValue) + " = " + cell.NumericCellValue));
cellContent = cell.NumericCellValue.ToString();
cellObject.cellType = Model.CellType.Numeric;
}
else if (cell.CellType == CellType.Formula)
{
//cellNodesList.Add(
// new TreeNode(nameof(cell.CellFormula) + " = " + cell.CellFormula));
cellContent = cell.CellFormula;
cellObject.cellType = Model.CellType.Formula;
}
else if (cell.CellType == CellType.Boolean)
{
//cellNodesList.Add(
// new TreeNode(nameof(cell.BooleanCellValue) + " = " + cell.BooleanCellValue));
cellContent = cell.BooleanCellValue.ToString();
cellObject.cellType = Model.CellType.Boolean;
}
cellObject.content = cellContent;
//cellNodesList.Add(new TreeNode(nameof(cell.CellComment) + " = " + cell.CellComment));
//var styleNode = new TreeNode("Cell : " + cell.ColumnIndex, cellNodesList.ToArray());
//_treeCellsList.Add(styleNode);
var element = new Element
{
cell = cellObject
};
elementsList.Add(element);
cellsList.Add(cellObject);
}
}
var line = new Line
{
ElementGroupsList = new List <ElementGroup>
{
new ElementGroup
{
elementsList = elementsList
}
}
};
linesList.Add(line);
}
SerializeObject(linesList);
//var treeNodeRow = new TreeNode("Row " + rowNum, _treeCellsList.ToArray());
//_treeRowsList.Add(treeNodeRow);
}
// Ajout des feuilles à la listView
var treeNode = new TreeNode("Sheet " + sheetIndex, _treeRowsList.ToArray());
treeView.Nodes.Add(treeNode);
}
return(treeView.Nodes);
}
private string GetCellValueAsString(ICell cell)
{
DataFormatter dataFormatter = new DataFormatter();
return(dataFormatter.FormatCellValue(cell, new HSSFFormulaEvaluator(cell.Sheet.Workbook)));
}
public static T GetValue <T>(this IRow row, int columnIndex)
{
var type = typeof(T);
var cell = row.GetCell(columnIndex, MissingCellPolicy.RETURN_NULL_AND_BLANK);
// null-handling
if (cell == null)
{
if (type.IsNullableType() || type == typeof(string))
{
return((T)(object)null);
}
else
{
throw new NotSupportedException($"Can't assign null value into '{type.FullName}'!");
}
}
// string handling
if (type == typeof(string))
{
// regular string entry
if (cell.CellType == CellType.String)
{
return((T)(object)cell.StringCellValue);
}
// formula with string result
if (cell.CellType == CellType.Formula && cell.CachedFormulaResultType == CellType.String)
{
return((T)(object)cell.StringCellValue);
}
// format value as string
var formatter = new DataFormatter();
var formattedValue = formatter.FormatCellValue(cell);
/*
* // replace non-breakign space with regular space (nbsp were uses with numeric formatting)
* formattedValue = formattedValue.Replace(Convert.ToChar(160), ' ');
*/
return((T)(object)formattedValue);
}
// fetching other value types
if (type.GetNonNullableType() == typeof(char))
{
return((T)(object)(char)cell.NumericCellValue);
}
else if (type.IsNumeric())
{
return((T)ChangeTypeEx(cell.NumericCellValue, type));
}
else if (type.IsDateTime())
{
return((T)(object)cell.DateCellValue);
}
else if (type.IsTimeSpan())
{
return((T)(object)TimeSpan.FromDays(cell.NumericCellValue));
}
else if (type.IsBool())
{
return((T)(object)cell.BooleanCellValue);
}
else
{
throw new NotSupportedException($"Can't handle type'{type.FullName}'!");
}
}
public void TestGetFormattedCellValueHSSFCell()
{
// Valid date formats -- cell values should be date formatted & not "555.555"
Row row = wb.GetSheetAt(0).GetRow(0);
IEnumerator it = row.GetCellEnumerator();
log("==== VALID DATE FORMATS ====");
while (it.MoveNext())
{
Cell cell = (Cell)it.Current;
log(formatter.FormatCellValue(cell));
// should not be equal to "555.555"
Assert.IsTrue(!"555.555".Equals(formatter.FormatCellValue(cell)));
// should contain "Jul" in the String
string result = formatter.FormatCellValue(cell);
Assert.IsTrue(result.IndexOf("Jul") > -1 || result.IndexOf("七月") > -1);
}
// Test number formats
row = wb.GetSheetAt(0).GetRow(1);
it = row.GetCellEnumerator();
log("\n==== VALID NUMBER FORMATS ====");
while (it.MoveNext())
{
Cell cell = (Cell)it.Current;
log(formatter.FormatCellValue(cell));
// should not be equal to "1234567890.12345"
Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell)));
}
// Test bad number formats
row = wb.GetSheetAt(0).GetRow(2);
it = row.GetCellEnumerator();
log("\n==== INVALID NUMBER FORMATS ====");
while (it.MoveNext())
{
Cell cell = (Cell)it.Current;
log(formatter.FormatCellValue(cell));
// should be equal to "1234567890.12345"
Assert.AreEqual("1234567890.12345", formatter.FormatCellValue(cell));
}
// Test Zip+4 format
row = wb.GetSheetAt(0).GetRow(3);
Cell cell2 = row.GetCell(0);
log("\n==== ZIP FORMAT ====");
log(formatter.FormatCellValue(cell2));
Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell2));
// Test phone number format
row = wb.GetSheetAt(0).GetRow(4);
cell2 = row.GetCell(0);
log("\n==== PHONE FORMAT ====");
log(formatter.FormatCellValue(cell2));
Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell2));
// Test SSN format
row = wb.GetSheetAt(0).GetRow(5);
cell2 = row.GetCell(0);
log("\n==== SSN FORMAT ====");
log(formatter.FormatCellValue(cell2));
Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell2));
// null Test-- null cell should result in empty String
Assert.AreEqual(formatter.FormatCellValue(null), "");
// null Test-- null cell should result in empty String
Assert.AreEqual(formatter.FormatCellValue(null), "");
}
public IActionResult Import()
{
if (!AuthenticationController.CheckToken(HttpContext, out string _))
{
return(Unauthorized());
}
IFormFile file = Request.Form.Files[0];
using (var stream = new MemoryStream())
{
// copy file into memory
file.CopyTo(stream);
// reset stream position
stream.Position = 0;
// create excel file abstraction over memory bytes
var excelFile = new XSSFWorkbook(stream);
var itemTypesTable = excelFile.GetSheet("ItemTypes");
var itemsTable = excelFile.GetSheet("Items");
var headerRow = itemTypesTable.GetRow(0).Cells;
List <int> itemTypesColumnIndices = new List <int>(headerRow.Count)
{
headerRow.Where(cell => cell.StringCellValue == "Name").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "UPC").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ImageURL").First().ColumnIndex
};
headerRow = itemsTable.GetRow(0).Cells;
List <int> itemsColumnIndices = new List <int>(headerRow.Count)
{
headerRow.Where(cell => cell.StringCellValue == "Id").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ItemTypeUPC").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "OrderNumberToSeller").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "OrderNumberToBuyer").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "QRCode").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ItemStatus").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "PricePaidBySeller").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "TaxPaidBySeller").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ShippingCostToSeller").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ShippingCostToBuyer").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "Fees").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "OtherExpenses").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "ShippingPaidByBuyer").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "PricePaidByBuyer").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "TimeStampOrdered").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "TimeStampReceived").First().ColumnIndex,
headerRow.Where(cell => cell.StringCellValue == "TimeStampSold").First().ColumnIndex
};
using var db = new DatabaseAccess();
// clear table
db.ItemTypes.RemoveRange(db.ItemTypes);
DataFormatter formatter = new DataFormatter();
var getStringValue = new Func <ICell, string>(cell =>
{
var val = formatter.FormatCellValue(cell);
return(string.IsNullOrEmpty(val) ? null : val);
});
for (int i = 1; i < itemTypesTable.PhysicalNumberOfRows; i++)
{
var row = itemTypesTable.GetRow(i);
var itemType = new ItemType
{
Name = getStringValue(row.GetCell(itemTypesColumnIndices[0])),
UPC = long.Parse(formatter.FormatCellValue(row.GetCell(itemTypesColumnIndices[1]))),
ImageURL = getStringValue(row.GetCell(itemTypesColumnIndices[2]))
};
db.ItemTypes.Add(itemType);
}
// clear table
db.Items.RemoveRange(db.Items);
for (int i = 1; i < itemsTable.PhysicalNumberOfRows; i++)
{
var row = itemsTable.GetRow(i);
var extractDate = new Func <ICell, DateTime>(cell =>
{
if (cell.CellType == CellType.Numeric && cell.NumericCellValue != -1)
{
return(cell.DateCellValue);
}
else
{
return(new DateTime());
}
});
var item = new Item
{
Id = 0,
ItemTypeUPC = long.Parse(formatter.FormatCellValue(row.GetCell(itemsColumnIndices[1]))),
OrderNumberToSeller = getStringValue(row.GetCell(itemsColumnIndices[2])),
OrderNumberToBuyer = getStringValue(row.GetCell(itemsColumnIndices[3])),
QRCode = getStringValue(row.GetCell(itemsColumnIndices[4])),
ItemStatus = (ItemStatus)Enum.Parse(typeof(ItemStatus), row.GetCell(itemsColumnIndices[5]).StringCellValue),
PricePaidBySeller = (decimal)row.GetCell(itemsColumnIndices[6]).NumericCellValue,
TaxPaidBySeller = (decimal)row.GetCell(itemsColumnIndices[7]).NumericCellValue,
ShippingCostToSeller = (decimal)row.GetCell(itemsColumnIndices[8]).NumericCellValue,
ShippingCostToBuyer = (decimal)row.GetCell(itemsColumnIndices[9]).NumericCellValue,
Fees = (decimal)row.GetCell(itemsColumnIndices[10]).NumericCellValue,
OtherExpenses = (decimal)row.GetCell(itemsColumnIndices[11]).NumericCellValue,
ShippingPaidByBuyer = (decimal)row.GetCell(itemsColumnIndices[12]).NumericCellValue,
PricePaidByBuyer = (decimal)row.GetCell(itemsColumnIndices[13]).NumericCellValue,
TimeStampOrdered = extractDate(row.GetCell(itemsColumnIndices[14])),
TimeStampReceived = extractDate(row.GetCell(itemsColumnIndices[15])),
TimeStampSold = extractDate(row.GetCell(itemsColumnIndices[16])),
};
db.Items.Add(item);
}
db.SaveChanges();
}
return(Accepted());
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public ActionResult ExcelImportData()
{
int SuccessCount = 0;
int ErrorCount = 0;
string virtualPath = "";
string fullFileName = "";
string error = string.Empty;
//读取上传文件
System.Web.HttpPostedFileBase pstFile = Request.Files["file"];
if (pstFile != null)
{
//上传文件
if (UploadifyFile(pstFile, ref virtualPath, ref fullFileName))
{
#region 读取Excel内容
IWorkbook workbook = null;
FileStream fs = null;
string fileName = fullFileName;
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
#region 读取Excel 导入到系统中
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheet("Sheet1");
if (sheet == null) //如果没有找到指定的sheetName对应的sheet ，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
//列名的集合
List <string> TitleList = new List <string>();
//导入薪资Excel的最后一列加上状态
ICell resultCell = firstRow.CreateCell(cellCount + 1);
resultCell.SetCellValue("导入结果");
#region 第一行是否是DataTable的列名
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue.Trim();
if (cellValue != null)
{
//添加到集合列中
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
TitleList.Add(cellValue);
}
}
}
//最后一列显示导入结果状态
DataColumn lastColumn = new DataColumn("结果");
data.Columns.Add(lastColumn);
startRow = sheet.FirstRowNum + 1;
#endregion
#region 导入内容
//获取当前用户ID
string currentPerson = SystemInfo.CurrentUserId;
//记录薪资项目和对应的值
Dictionary <string, string> dicList = new Dictionary <string, string>();
#region 循环所有行数
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
if (row.FirstCellNum > 0)
{
continue; //不是从第一个单元格开始
}
DataRow dataRow = data.NewRow();
//清除项目
dicList.Clear();
error = string.Empty;
ContractEntity entity = new ContractEntity();
List <ContractServiceEntity> serviceEntityList = new List <ContractServiceEntity>();
try
{
#region 每行的列
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
string CellValue = string.Empty;
#region 获取单元格的值
switch (row.GetCell(j).CellType)
{
case CellType.Blank:
case CellType.Error:
case CellType.Unknown:
break;
case CellType.Boolean:
CellValue = row.GetCell(j).BooleanCellValue ? "1" : "0";
break;
case CellType.Formula:
//单元格函数
CellValue = row.GetCell(j).CellFormula;
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
DataFormatter formatter = new DataFormatter();
CellValue = formatter.FormatCellValue(row.GetCell(j));
}
else
{
CellValue = row.GetCell(j).NumericCellValue.ToString();
}
break;
case CellType.Numeric:
//数字类型
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
DataFormatter formatter = new DataFormatter();
CellValue = formatter.FormatCellValue(row.GetCell(j));
}
else
{
CellValue = row.GetCell(j).NumericCellValue.ToString();
}
break;
case CellType.String:
//字符串
CellValue = row.GetCell(j).StringCellValue;
break;
default:
break;
}
#endregion
dataRow[j] = CellValue;
#region 务类型
if (TitleList[j] == "服务类型")
{
string[] strArr = CellValue.Split(new char[] { ',', ';', '^', '|', '，' }, StringSplitOptions.RemoveEmptyEntries);
if (strArr.Length > 0)
{
for (int q = 0; q < strArr.Length; q++)
{
var type = strArr[q];
var productInfo = itemCache.GetDataItemList("Client_ProductInfo").FirstOrDefault(s => s.ItemName == type);
if (productInfo != null)
{
ContractServiceEntity serviceEntity = new ContractServiceEntity();
serviceEntity.ServiceTypeId = productInfo.ItemDetailId;
serviceEntity.ServiceType = productInfo.ItemName;
serviceEntity.ContractId = "";
serviceEntity.Create();
serviceEntityList.Add(serviceEntity);
//合同编码的前置
if (string.IsNullOrWhiteSpace(entity.ContractCode))
{
var dataItem = itemCache.GetDataItem(serviceEntity.ServiceTypeId);
if (dataItem != null)
{
entity.ContractCode = dataItem.ItemValue;
}
}
}
}
}
if (serviceEntityList.Count < 1)
{
error = "服务类型数据有误";
break;
}
var serviceList = serviceEntityList.Select(s => s.ServiceType);
entity.ServiceType = string.Join(",", serviceList);
}
#endregion
error = SetEntityValue(entity, TitleList[j], CellValue.Trim());
if (!string.IsNullOrWhiteSpace(error))
{
break;
}
}
}
#endregion
if (error == string.Empty)
{
string moduleCode = string.Empty;
if (entity.ContractType == 21)
{
moduleCode = "10007";
}
else
{
moduleCode = "10008";
}
contractbll.AddForm(entity, serviceEntityList, "", moduleCode);
error = "导入成功";
SuccessCount++;
}
else
{
ErrorCount++;
}
}
catch (Exception ex)
{
error = ex.Message;
ErrorCount++;
}
dataRow[cellCount] = error;
data.Rows.Add(dataRow);
ICell cell = row.CreateCell(cellCount + 1);
cell.SetCellValue(error);
}
#endregion
#endregion
}
ViewBag.ResultImportData = data;
}
catch (Exception ex)
{
Response.Write("批量导入客户异常1: " + ex.Message);
error = "批量导入客户异常: " + ex.Message;
}
finally
{
if (fs != null)
{
fs.Close();
}
fs = null;
}
#endregion
try
{
using (FileStream fileWrite = new FileStream(fullFileName, FileMode.Create))
{
workbook.Write(fileWrite);
}
}
catch (Exception ex)
{
}
#endregion
}
}
error = "导入数据不存在，请返回批量导入页面！";
ViewBag.Error = error;
ViewBag.Url = virtualPath;
ViewBag.ErrorCount = ErrorCount;
ViewBag.SuccessCount = SuccessCount;
return(View("ExportError"));
}
private static Classificacoes ReadClassificacoes(ISheet sheet, int numValores)
{
int startCol = 15;
//Ler Cabecalho dos tipos doc
IRow rowCabecalho = sheet.GetRow(1);
string[] cabecalhoTiposDoc = new string[40]; //Diz o tipo de documento por coluna (indexado pela coluna)
for (int col = startCol; col <= startCol + 20; col++)
{
var cell = rowCabecalho.GetCell(col);
if (cell == null)
{
cabecalhoTiposDoc[col] = null;
break;
}
cabecalhoTiposDoc[col] = cell.StringCellValue;
}
Classificacoes classificacoes = new Classificacoes();
//Lê as contas
ContasParaValor contasBase0 = ReadContasParaValor(sheet.GetRow(2), startCol, cabecalhoTiposDoc, "Base0");
ContasParaValor contasBase1 = ReadContasParaValor(sheet.GetRow(3), startCol, cabecalhoTiposDoc, "Base1");
ContasParaValor contasBase2 = ReadContasParaValor(sheet.GetRow(4), startCol, cabecalhoTiposDoc, "Base2");
ContasParaValor contasBase3 = ReadContasParaValor(sheet.GetRow(5), startCol, cabecalhoTiposDoc, "Base3");
ContasParaValor contasIva1 = ReadContasParaValor(sheet.GetRow(6), startCol, cabecalhoTiposDoc, "Iva1");
ContasParaValor contasIva2 = ReadContasParaValor(sheet.GetRow(7), startCol, cabecalhoTiposDoc, "Iva2");
ContasParaValor contasIva3 = ReadContasParaValor(sheet.GetRow(8), startCol, cabecalhoTiposDoc, "Iva3");
ContasParaValor contasRetIva = ReadContasParaValor(sheet.GetRow(9), startCol, cabecalhoTiposDoc, "RetIva");
ContasParaValor contasTotal = ReadContasParaValor(sheet.GetRow(10), startCol, cabecalhoTiposDoc, "Total");
classificacoes.contasPorValor = new ContasParaValor[] {
contasBase0,
contasBase1,
contasBase2,
contasBase3,
contasIva1,
contasIva2,
contasIva3,
contasRetIva,
contasTotal
};
//Lê os diarios codigos de documento para cada tipo de documento
for (int rowIdx = 12; rowIdx <= sheet.LastRowNum; rowIdx++)
{
IRow row = sheet.GetRow(rowIdx);
if (row != null) //Se a linha tiver dados continua
{
string tipoDoc = row.GetCell(14).StringCellValue;
if (tipoDoc == null || tipoDoc.Trim().Length == 0)
{
break;
}
//Obtem o codigo de documento e dirio para este tipoDoc
string codigoDoc = df.FormatCellValue(row.GetCell(15));
string diario = df.FormatCellValue(row.GetCell(16));
classificacoes.diarioECodigoDocPorTipoDoc.Add(tipoDoc, new Tuple <string, string>(diario, codigoDoc));
}
}
return(classificacoes);
}
/// <summary>
/// Reads consumption-compilant portions of Data from the underlaying data-source
/// </summary>
/// <returns>an IEnumerable of the base-set</returns>
protected override IEnumerable <IDictionary <string, ExcelDataRecord> > ReadData()
{
IWorkbook book = OpenWorkbook(sourceFile);
bool foundSheet = false;
for (int i = 0; i < book.NumberOfSheets; i++)
{
string msg;
ISheet sheet = book.GetSheetAt(i);
if ((ImportWorkSheetDecider.Decide(sheet.SheetName, DecisionMethod.Simple, out msg) & (DecisionResult.Acceptable | DecisionResult.Success)) != DecisionResult.None)
{
foundSheet = true;
yield return
(new Dictionary <string, ExcelDataRecord>
{
{
"SheetName",
new ExcelDataRecord {
FormattedText = sheet.SheetName, RawData = sheet.SheetName
}
}
});
DataFormatter f = new DataFormatter();
foreach (IRow row in sheet)
{
if (row != null)
{
Dictionary <string, ExcelDataRecord> vals = new Dictionary <string, ExcelDataRecord>();
foreach (ICell cell in row.Cells)
{
XLNumber num = cell.ColumnIndex;
object value = GetCellValue(cell, cell.CellType);
if (value != null)
{
vals.Add(num.ToString(),
new ExcelDataRecord {
RawData = value, FormattedText = f.FormatCellValue(cell)
});
}
else if (readNullData)
{
vals.Add(num.ToString(), new ExcelDataRecord {
RawData = null, FormattedText = ""
});
}
}
string origin = $"{sheet.SheetName}::{row.RowNum}";
vals.Add("$origin", new ExcelDataRecord {
FormattedText = origin, RawData = origin
});
yield return(vals);
}
}
}
}
if (!foundSheet)
{
LogParserEvent(null, $@"No sheet has met the configured Constraints.
Settings for sheet-search: {ImportWorkSheetDecider}", ParserEventSeverity.Warning, null);
}
}
//將excel 存到list 給controller
public AttachSingleViewModel ShowExcelFile(Attachment attach)
{
FileStream fs = null;
//NPOI
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
DataFormatter dataFormatter = new DataFormatter(CultureInfo.CurrentCulture);
//插入詳細資訊
AttachSingleViewModel viewModel = new AttachSingleViewModel()
{
AttachDetail = attach
};
//2維列表
AttachXmlViewModel xmlViewModel = null;
AttachXmlRow rowViewModel = null;
//path
var physicalPath = HttpContext.Current.Server.MapPath(attach.AttachmentPath);
var path = Path.Combine(physicalPath, attach.AttachmentName);
//圖片檔
Regex regex = new Regex(@"\.(jpg|jpeg|png|gif)$", RegexOptions.IgnoreCase);
if (regex.IsMatch(attach.AttachmentType))
{
viewModel.AttahcFilePath = Path.Combine(attach.AttachmentPath, attach.AttachmentName);
}
try
{
//開啟現有檔案來讀取，存到資料串流
using (fs = File.OpenRead(path))
{
//資料串流存到NPOI物件
//新excel
if (attach.AttachmentType == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
///舊excel
if (attach.AttachmentType == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(0); //NPOI sheet
xmlViewModel = new AttachXmlViewModel();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;
if (rowCount > 20)
{
rowCount = 20;
}
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0); //NPOI row
int cellCount = firstRow.LastCellNum;
for (int i = 0; i < rowCount; i++)
{
row = sheet.GetRow(i);
if (row == null)
{
continue;
}
rowViewModel = new AttachXmlRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
cell = row.GetCell(j); //NPOI column
if (cell == null)
{
rowViewModel.XmlRow.Add("");
}
else
{
rowViewModel.XmlRow.Add(dataFormatter.FormatCellValue(cell));
}
}
//插入列表 row
xmlViewModel.Table.Add(rowViewModel);
}
}
}
}
//插入整個列表
viewModel.AttachFile = xmlViewModel;
return(viewModel);
}
}
catch (Exception e)
{
if (fs != null)
{
fs.Close();
}
return(viewModel);
}
}
// /**
// * Drawing context to measure text
// */
//private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true);
/**
* Compute width of a column and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells)
{
//AttributedString str;
//TextLayout layout;
IWorkbook wb = sheet.Workbook;
DataFormatter formatter = new DataFormatter();
IFont defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString((defaultChar));
//copyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.Iterator, fontRenderContext);
//int defaultCharWidth = (int)layout.Advance;
Font font = IFont2Font(defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
//rows:
bool skipthisrow = false;
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();)
{
IRow row = (IRow)it.Current;
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
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.
//continue rows;
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
ICellStyle style = cell.CellStyle;
Chama.Utils.NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex);
CellType cellType = cell.CellType;
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.FORMULA)
{
cellType = cell.CachedFormulaResultType;
}
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);
font = IFont2Font(font1);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.Iterator, 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.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
SizeF sf = g.MeasureString(txt, font);
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).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).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("F", CultureInfo.InvariantCulture);
}
}
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);
//layout = new TextLayout(str.Iterator, 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.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
SizeF sf = g.MeasureString(txt, font);
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.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
}
return(width);
}
