public string UploadHandler()
{
HttpPostedFile httpPostedFileBase = System.Web.HttpContext.Current.Request.Files[0];
if (httpPostedFileBase != null)
{
string[] file = httpPostedFileBase.FileName.Split('.');
if (file.Length > 0)
{
if ((file[file.Length - 1].ToString()) == "xlsx" || (file[file.Length - 1].ToString() == "xls"))
{
DataTable dt;
ExcelFileReader xlReader = new ExcelFileReader();
dt = xlReader.GetExcelDataTable(HttpContext.Request.Files[0]);
TempData["ExcelData"] = dt;
return(httpPostedFileBase.FileName);
}
}
}
else
{
return("1");
}
return("0");
}
private bool ReadRow(ExcelWorksheet excelWorksheet, ExcelFileReader reader, int lineIndex, TParsed row)
{
bool isEmptyRow = true;
switch (reader.DatasetOrientation)
{
case DataOrientation.Horizontal:
for (int i = 0; i < reader.DataRange.Rows; i++)
{
if (reader.PropertySetters[i].SetValue(excelWorksheet, row, i + reader.DataRange.Start.Row, lineIndex + reader.DataRange.Start.Column))
{
isEmptyRow = false;
}
}
break;
case DataOrientation.Vertical:
for (int i = 0; i < reader.DataRange.Columns; i++)
{
if (reader.PropertySetters[i].SetValue(excelWorksheet, row, lineIndex + reader.DataRange.Start.Row, i + reader.DataRange.Start.Column))
{
isEmptyRow = false;
}
}
break;
default:
break;
}
return(!isEmptyRow);
}
/// <summary>
/// The main method
/// </summary>
/// <param name="args">The arguments.</param>
static void Main(string[] args)
{
// Retrieve project path
string projectPath = AppDomain.CurrentDomain.BaseDirectory.Substring(0, AppDomain.CurrentDomain.BaseDirectory.LastIndexOf("bin"));
// Retrieve template document.
var templatePath = $"{projectPath}\\Template\\UserList.xlsx";
var fileAsStream = new MemoryStream(File.ReadAllBytes(templatePath));
// Import file with ExcelFileReader From File info to List of dictionary with header
List <Dictionary <string, object> > dictionaryResultFromFileInfo = ExcelFileReader.ImportFileAsDictionary(new FileInfo(templatePath), "Users", true);
// Import file with ExcelFileReader From File Stream to List of dictionary without header
List <Dictionary <string, object> > dictionaryResultFromFileStream = ExcelFileReader.ImportFileAsDictionary(fileAsStream, "Users");
// Import file with ExcelFileReader From File info to Data table
DataTable dataTableResultFromFileInfo = ExcelFileReader.ImportFileAsDataTable(new FileInfo(templatePath), "Users");
// Import file with ExcelFileReader From File Stream to Data table
DataTable dataTableResultFromFileStream = ExcelFileReader.ImportFileAsDataTable(fileAsStream, "Users");
// Export file with ExcelFileWriter
string tempFilePath = $"{projectPath}\\App_Data\\{Guid.NewGuid()}.xlsx";
var streamExport = ExcelFileWriter.ExportToExcel(dataTableResultFromFileStream, "Users");
FileStream file = new FileStream(tempFilePath, FileMode.Create, FileAccess.Write);
streamExport.WriteTo(file);
file.Close();
streamExport.Close();
Console.Write($"Generated file path : {tempFilePath}");
Console.ReadLine();
}
private void btnLoadExcel_Click(object sender, EventArgs e)
{
if (opfLoadExcel.ShowDialog() == DialogResult.OK)
{
DataTable loadExcelData = ExcelFileReader.ReadExcelSheet(opfLoadExcel.FileName, true);
this.dataGridView1.DataSource = loadExcelData;
}
}
static void Main(string[] args)
{
string excelfile = Environment.CurrentDirectory.Replace("\\bin\\Debug", "\\ConditionsExcel.xlsx");
using (dynamic rFile = new ExcelFileReader <dynamic>(excelfile)) {
var ExcelSheet = new List <string[]>();
foreach (var line in rFile)
{
string[] ConditionLine = { line.Name, line.Explanation, line.Action, line.Get("Commonly known as"), line.Abbrevations, line.Get("See also") };
ExcelSheet.Add(ConditionLine);
}
var data = ExcelSheet.ToArray();
for (int i = 0; i <= data.Length - 1; i++)
{
var Array = data[i];
for (int j = 0; j <= Array.Length - 1; j++)
{
Console.WriteLine(j + " " + Array[j]);
}
string boundaries = "***********************";
Console.WriteLine(boundaries);
Console.ReadLine();
}
}
/*
*
*
* StreamReader sr = new StreamReader(filepath);
* var lines = new List<string[]>();
* int Row = 0;
* while (!sr.EndOfStream) {
* string[] Line = sr.ReadLine().Split(',');
*
* lines.Add(Line);
* Row++;
* }
* var data = lines.ToArray();
*
* for (int i = 0; i <= data.Length - 1; i++) {
* var Array = data[i];
* for (int j = 0; j <= Array.Length - 1; j++) {
*
* Console.WriteLine(j + " " + Array[j]);
* }
* string boundaries = "***********************";
* Console.WriteLine(boundaries);
* Console.ReadLine();
* }
*
*
*
*/
Console.ReadLine();
}
public void UpdateAttributes()
{
var FilePath = "E:\\Plottefil\\PlottingFileConfig.xlsx";
var ExcelReader = new ExcelFileReader(FilePath);
var Settings = ExcelReader.ReadFile();
var CAC = new ChangeAttributes();
//CAC.UPDATEATTRIBUTES(Settings, Settings.DrawingData[0].revDate.ToShortDateString());
}
public void t2()
{
var reader = new ExcelFileReader();
var itmes = reader.ReadDistinctDisciplines(
@"D:\dev\C#\ExposurePresenter\XLSInputFiles\PLK Grzegorz branże.xlsx").ToArray();
// var db = new ExposureDb();
// foreach (var item in itmes)
// {
// db.ExposureRecords.Add(item);
// }
}
public void CanCountColumnsOnFirstRow()
{
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx");
reader.SetActiveSheet("ElementData");
reader.ReadRow();
Assert.That(reader.GetColumnCount(), Is.EqualTo(5));
}
public void WillReturnFalseIfColumnDoesntExist()
{
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx");
reader.SetActiveSheet("ElementData");
reader.ReadRow();
Assert.That(reader.GetColumnCount(), Is.EqualTo(5));
string result = "";
Assert.That(reader.ReadColumnAsText(8, out result), Is.False);
Assert.That(result, Is.EqualTo(""));
}
public void PopulateData(string filePath, ExposureDb db)
{
var fileReader = new ExcelFileReader();
db.Branches.AddOrUpdate(branch => branch.Name, fileReader.ReadDistinctBrnaches(filePath).ToArray());
db.Brands.AddOrUpdate(brand => brand.Name, fileReader.ReadDistinctBrands(filePath).ToArray());
db.Disciplines.AddOrUpdate(discipline => discipline.Name,
fileReader.ReadDistinctDisciplines(filePath).ToArray());
var records = fileReader.ReadExposureRecords(filePath).ToArray();
foreach (var exposureRecord in records)
{
db.ExposureRecords.AddOrUpdate(record => new { record.Brand, record.Month }, exposureRecord);
db.SaveChanges();
}
}
public void CanReadInt()
{
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx");
reader.SetActiveSheet("ElementData");
reader.ReadRow();
reader.ReadRow();
int result = 0;
Assert.That(reader.ReadColumnAsInt(0, out result), Is.True);
Assert.That(result, Is.EqualTo(28));
Assert.That(reader.ReadColumnAsInt(1, out result), Is.True);
Assert.That(result, Is.EqualTo(3024));
Assert.That(reader.ReadColumnAsInt(3, out result), Is.True);
Assert.That(result, Is.EqualTo(302428));
}
public MainWindow()
{
var windowThrottleInterval = TimeSpan.FromSeconds(1);
_windowLocationSaveMethod = new MethodThrottle(SaveWindowLocation, windowThrottleInterval);
_windowSizeSaveMethod = new MethodThrottle <Size>(SaveWindowSize, windowThrottleInterval);
_windowStateSaveMethod = new MethodThrottle(SaveWindowState, windowThrottleInterval);
_documentFileReader = new DocumentFileReader();
_documentFileWriter = new DocumentFileWriter();
_excelFileReader = new ExcelFileReader();
_resXFileReader = new ResXFileReader();
_resXFileWriter = new ResXFileWriter();
InitializeComponent();
SetupInitialWindow();
LoadCommandLineFile();
UpdateTitle();
}
public void CanReadDecimal()
{
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx");
reader.SetActiveSheet("DesignData");
reader.ReadRow();
reader.ReadRow();
decimal result = 0;
Assert.That(reader.ReadColumnAsDecimal(5, out result), Is.True);
Assert.That(result, Is.EqualTo(0.4d));
reader.ReadRow();
Assert.That(reader.ReadColumnAsDecimal(5, out result), Is.True);
Assert.That(result, Is.EqualTo(1.2d));
Assert.That(reader.ReadColumnAsDecimal(6, out result), Is.True);
Assert.That(result, Is.EqualTo(0.4d));
}
private bool ReadRow(ExcelWorksheet excelWorksheet, ExcelFileReader reader, int lineIndex, IDictionary <string, object> row)
{
//TODO: better exception handling here
bool isEmptyRow = true;
switch (reader.DatasetOrientation)
{
case DataOrientation.Horizontal:
for (int i = 0; i < reader.DataRange.Rows; i++)
{
if (reader.PropertySetters.TryGetValue(i, out var propertySetter))
{
if (propertySetter.SetValue(excelWorksheet, i + reader.DataRange.Start.Row, lineIndex + reader.DataRange.Start.Column))
{
isEmptyRow = false;
row[propertySetter.PropertyInfo.Name] = propertySetter.ParsedValue;
}
}
}
break;
case DataOrientation.Vertical:
for (int i = 0; i < reader.DataRange.Columns; i++)
{
if (reader.PropertySetters.TryGetValue(i, out var propertySetter))
{
if (propertySetter.SetValue(excelWorksheet, lineIndex + reader.DataRange.Start.Row, i + reader.DataRange.Start.Column))
{
isEmptyRow = false;
row[propertySetter.PropertyInfo.Name] = propertySetter.ParsedValue;
}
}
}
break;
default:
break;
}
return(!isEmptyRow);
}
public void CanReadSeveralRows()
{
var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx");
reader.SetActiveSheet("ElementData");
reader.ReadRow();
Assert.That(reader.GetColumnCount(), Is.EqualTo(5));
string result = "";
Assert.That(reader.ReadColumnAsText(0, out result), Is.True);
Assert.That(result, Is.EqualTo("MaterialID"));
reader.ReadRow();
Assert.That(reader.ReadColumnAsText(0, out result), Is.True);
Assert.That(result, Is.EqualTo("28"));
reader.ReadRow();
Assert.That(reader.ReadColumnAsText(0, out result), Is.True);
Assert.That(result, Is.EqualTo("37"));
}
private ExcelFileProcess ProcessExpenseSheet(string path, string fileName)
{
logger.DebugFormat("Processing Expense Sheet Name [{0}]", fileName);
DataSet ds = ExcelFileReader.Read(path);
DataTable dataTable = ds.Tables[0];
dataTable = dataTable.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable();
if (dataTable.Rows.Count == 0)
{
logger.DebugFormat("No rows found in file [{0}]", fileName);
return(new ExcelFileProcess {
Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error
});
}
string missingcolumn = ExcelFileReader.CheckAllColumnExist(dataTable, ColumnList);
if (!string.IsNullOrWhiteSpace(missingcolumn))
{
logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn));
return(new ExcelFileProcess {
Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error
});
}
string info = ExcelFileReader.ValidateData(dataTable, MandatoryColumnList);
if (!string.IsNullOrWhiteSpace(info))
{
logger.Debug(info);
return(new ExcelFileProcess {
Message = info, Response = false, MessageType = MessageClass.Error
});
}
string errors = ExcelFileReader.ValidateDataFormat(dataTable);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
var userCards = userCardManagement.GetAllUserCards();
string accNumberError = ExcelFileReader.ValidateAccountNumber(dataTable, userCards);
if (!string.IsNullOrWhiteSpace(accNumberError))
{
logger.Debug(accNumberError);
return(new ExcelFileProcess {
Message = accNumberError, Response = false, MessageType = MessageClass.Error
});
}
ExcelFileProcess process = InsertData(dataTable, fileName, userCards);
return(process);
}
public void Setup()
{
_fileReader = new ExcelFileReader();
}
public void ReadExcelFile()
{
ExcelFileReader EFR = new ExcelFileReader("O:\\A070000\\A074115\\CAD\\E40 RIE\\E99\\PlottingFileConfig.xlsx");
EFR.ReadFile();
}
private ExcelFileProcess ProcessExpenseSheet(string path, string fileName, DateTime expenseDate)
{
logger.DebugFormat("Processing User Expense Sheet Name [{0}]", fileName);
DataSet ds = ExcelFileReader.Read(path);
DataTable table = ds.Tables[0];
table = table.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable();
if (table.Rows.Count == 0)
{
logger.DebugFormat("No rows found in file [{0}]", fileName);
return(new ExcelFileProcess {
Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error
});
}
string missingcolumn = ExcelFileReader.CheckAllColumnExist(table, ColumnList);
if (!string.IsNullOrWhiteSpace(missingcolumn))
{
logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn));
return(new ExcelFileProcess {
Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error
});
}
string info = ExcelFileReader.ValidateData(table, MandatoryColumnList);
if (!string.IsNullOrWhiteSpace(info))
{
logger.Debug(info);
return(new ExcelFileProcess {
Message = info, Response = false, MessageType = MessageClass.Error
});
}
string errors = ExcelFileReader.ValidateAmount(table, ValidateColumnList);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
var department = departmentManagement.GetAllDepartments();
var allocation = userAllocationManagement.GetAllUsersActiveAllocations();
var users = UserManager.Users.Where(x => !x.IsDeleted).ToList();
errors = ExcelFileReader.ValidateDepartmentAndUsersAndAllocation(table, department, users, allocation);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
ExcelFileProcess process = InsertData(table, fileName, users, expenseDate, allocation);
return(process);
}
public ProductInfoReader()
{
excelReader = new ExcelFileReader();
}
public bool Convert(TableInfo info)
{
Debug.Log(">>转换表格：【" + info.m_RealName + "】开始！！！" + info.m_FullName);
m_TabInfo = info;
m_WorkBook = ExcelFileReader.ReadExcelFile(m_TabInfo.m_FullName, "");
if (m_WorkBook == null)
{
string infoStr = "表格：【" + m_TabInfo.m_RealName + "】无法打开. 请检查该表是否被其他程序占用！！！";
Debug.LogError(infoStr);
if (EditorUtility.DisplayDialog("提示", infoStr, "确定", "停止"))
{
return(true);
}
else
{
return(false);
}
}
//return true;
m_Sheet = m_WorkBook.GetSheetAt(0);
if (m_Sheet == null)
{
string infoStr = "表格：【" + m_TabInfo.m_RealName + "】数据为空. 请检查内部是否有数据！！！\n";
Debug.LogError(infoStr);
if (EditorUtility.DisplayDialog("提示", infoStr, "确定", "停止"))
{
return(true);
}
else
{
return(false);
}
}
m_FirstRowIdx = m_Sheet.FirstRowNum;
m_LastRowIdx = m_Sheet.LastRowNum;
m_NameRow = m_Sheet.GetRow(0);
m_TypeRow = m_Sheet.GetRow(1);
m_FirstColumIdx = m_NameRow.FirstCellNum;
m_LastColumIdx = m_NameRow.LastCellNum;
// 确定有效行索引和列索引
IRow rowData = null;
for (int row = 3; row <= m_LastRowIdx; ++row)
{
rowData = m_Sheet.GetRow(row);
// 遇到空行则跳过
if (rowData == null)
{
m_LastRowIdx = row - 1;
break;
}
// 如果该行无数据则跳过
if (rowData.GetCell(0) == null)
{
m_LastRowIdx = row - 1;
break;
}
if (string.IsNullOrEmpty(rowData.GetCell(0).ToString()))
{
m_LastRowIdx = row - 1;
break;
}
}
ICell cellData = null;
for (int cell = m_FirstColumIdx; cell <= m_LastColumIdx; ++cell)
{
cellData = m_NameRow.GetCell(cell);
if (cellData == null)
{
m_LastColumIdx = cell - 1;
break;
}
string varName = cellData.ToString();
if (string.IsNullOrEmpty(varName))
{
m_LastColumIdx = cell - 1;
break;
}
}
// 填充数据
m_Names.Clear();
m_Types.Clear();
HashSet <string> nameSet = new HashSet <string>();
string dataStr = "";
for (int cell = m_FirstColumIdx; cell <= m_LastColumIdx; ++cell)
{
cellData = m_NameRow.GetCell(cell);
string varName = cellData.ToString();
if (string.IsNullOrEmpty(varName))
{
break;
}
if (nameSet.Contains(varName))
{
string infoStr = "表格：【" + m_TabInfo.m_RealName + "】第" + cell + "列数据名称重复和前面的重复：【" + varName + "】. 请检查！！！\n注意，行列索引从0开始！！！";
Debug.LogError(infoStr);
EditorUtility.DisplayDialog("提示", infoStr, "确定");
return(false);
}
else
{
if (cell == m_SkipColumn)
{
varName += "_Skipped";
}
varName = HandleName(varName);
nameSet.Add(varName);
m_Names.Add(varName);
}
// 处理类型
cellData = m_TypeRow.GetCell(cell);
dataStr = cellData.ToString();
// 去掉可能的空格
dataStr = dataStr.Replace(" ", "");
// 全部转为大写
dataStr = dataStr.ToUpper();
DataType type = GetValidType(dataStr);
if (type == DataType.NONE)
{
string infoStr = "表格：【" + m_TabInfo.m_RealName + "】第" + cell + "列数据类型不对：【" + cellData.ToString() + "】. 请检查！！！\n注意，行列索引从0开始！！！";
Debug.LogError(infoStr);
EditorUtility.DisplayDialog("提示", infoStr, "确定");
return(false);
}
m_Types.Add(type);
}
// 导出数据
if (!ExportTXT())
{
return(false);
}
// 导出脚本
if (!ExportScript())
{
return(false);
}
Debug.Log(">>转换表格：【" + info.m_RealName + "】完成！！！");
return(true);
}
