public bool PrepareExcel(string timeInUse = null)
{
List <string> dataPointsInUse;
if (timeInUse != null)
{
dataPointsInUse = new List <string> {
timeInUse
};
dataPointsInUse = GetDataPointsInUse(dataPointsInUse, "excelCol", "excelCol");
}
else
{
dataPointsInUse = GetDataPointsInUse("excelCol", "excelCol");
}
if (dataPointsInUse.Count == 0)
{
return(true);
}
ExcelProcessor excelProcessor = new ExcelProcessor();
try {
dataRecords = excelProcessor.GetRecords(dataFile, excelSheet, dataPointsInUse, "Text", null, int.Parse(excelRowStart), int.Parse(excelRowEnd), true);
} catch (Exception ex) {
sourceLogger.Error(ex, "Error retrieving Excel data.");
return(false);
}
return(true);
}
private List <MakeModel> GetList()
{
var processor = new ExcelProcessor(_excelFile);
var models = processor.GetRefData();
return(models);
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (_tabControl1.SelectedTab == null)
{
return;
}
TabPageTag tag = _tabControl1.SelectedTab.Tag as TabPageTag;
cbxFIlePath.SelectedItem = tag.FileName;
this._currentDataGridView = _tabControl1.SelectedTab.Controls["guidview"] as DataGridView;
this._currentExcelProcessor = tag.ExcelProcessor;
}
private void DropObject(object sender, DragEventArgs e)
{
var filename = ((string[])e.Data.GetData(DataFormats.FileDrop, true)).FirstOrDefault();
if (filename != null)
{
using (var processor = new ExcelProcessor(filename))
{
var problems = new ProblemMapping(processor.FetchProblems());
var students = problems.GetFillers(processor.FetchStudents());
SaveToCsv(problems, students);
}
}
}
static void Main(string[] args)
{
//Test files located in 'test_data' folder
//[Normal Run]
//-----------------------
DataSet dsNPOIxlsx = ExcelProcessor.GetDataNPOI(getPhysicalFilename("test_data_1.xlsx"));
DataSet dsNPOIxls = ExcelProcessor.GetDataNPOI(getPhysicalFilename("test_data_1.xls"));
DataSet dsOLEDBxlsx = ExcelProcessor.GetDataOLEDB(getPhysicalFilename("test_data_1.xlsx"));
DataSet dsOLEDBxls = ExcelProcessor.GetDataOLEDB(getPhysicalFilename("test_data_1.xls"));
//[Populate column names from row (header row)]
//-----------------------
Dictionary <string, int> headersNPOI = new Dictionary <string, int>();
headersNPOI.Add("Source Data", 0);
headersNPOI.Add("Sample PivotTable Report", 4);
DataSet dsNPOIWithHeader = ExcelProcessor.GetDataNPOI(
getPhysicalFilename("test_data_1.xlsx"),
headersNPOI
);
Dictionary <string, int> headersOLEDB = new Dictionary <string, int>();
headersOLEDB.Add("Source Data", 0);
headersOLEDB.Add("Sample PivotTable Report", 1);
DataSet dsOLEDBWithHeader = ExcelProcessor.GetDataOLEDB(
getPhysicalFilename("test_data_1.xlsx"),
headersOLEDB
);
//[Swapping file extensions (if file has wrong extension app will swap an retry)]
//-----------------------
DataSet dsNPOIxlsSwap = ExcelProcessor.GetDataNPOI(getPhysicalFilename("test_data_2_actually_xls.xlsx"));
DataSet dsNPOIxlsxSwap = ExcelProcessor.GetDataNPOI(getPhysicalFilename("test_data_2_actually_xlsx.xls"));
DataSet dsOLEDBxlsSwap = ExcelProcessor.GetDataOLEDB(getPhysicalFilename("test_data_2_actually_xls.xlsx"));
DataSet dsOLEDBxlsxSwap = ExcelProcessor.GetDataOLEDB(getPhysicalFilename("test_data_2_actually_xlsx.xls"));
}
protected void Page_Load(object sender, EventArgs e)
{
if (this.Request.Files.Count > 0)
{
string guid = Guid.NewGuid().ToString();
string filePath = "//WorkTime//InputExcelFiles//" + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(Request.Files[0].FileName);
this.Request.Files[0].SaveAs(Server.MapPath(filePath));
ExcelProcessor ep = ExcelService.GetProcessor(Server.MapPath(filePath));
DataSet ds = ep.GetDataSet();
InputDatas(ds.Tables[0]);
Response.Write("{success:true}");
Response.End();
}
id = RequestData.Get <string>("id", String.Empty);
type = RequestData.Get <string>("type", String.Empty);
SearchCriterion.AutoOrder = false;
SearchCriterion.SetOrder(SysUser.Prop_WorkNo);
users = SysUserRule.FindAll(SearchCriterion);
this.PageState.Add("UsrList", users);
SysUser usr = null;
switch (this.RequestAction)
{
case RequestActionEnum.Create:
usr = this.GetPostedData <SysUser>();
usr.DoCreate();
this.SetMessage("新建成功！");
break;
case RequestActionEnum.Update:
usr = this.GetMergedData <SysUser>();
usr.DoUpdate();
this.SetMessage("保存成功！");
break;
case RequestActionEnum.Delete:
usr = this.GetTargetData <SysUser>();
usr.DoDelete();
this.SetMessage("删除成功！");
break;
}
}
private void OnGUI()
{
EditorGUIUtility.labelWidth = 200;
GUILayout.Label($"Workbook Settings for {_workbooks.Length} workbook(s)", EditorStyles.boldLabel);
GUILayout.Space(25);
GUILayout.Label("Checking this, will prefix all import assets with the workbook name", EditorStyles.boldLabel);
PrefixAssetNames = EditorGUILayout.Toggle("Prefix Asset Names", PrefixAssetNames);
GUILayout.Space(50);
if (GUILayout.Button("Generate Importer"))
{
SetupWorkbooks();
ExcelProcessor.Process(_workbooks);
Close();
}
}
private void ProcessFileSelection(OpenFileDialog openFileDialog)
{
ShowProgressBar(true);
List <Task> Tasks = new List <Task>();
foreach (var filePath in openFileDialog.FileNames)
{
ExcelProcessor ep = new ExcelProcessor();
StatementProcessor sp = new StatementProcessor();
Progress.Add(ep.Progress);
Progress.Add(sp.Progress);
Task t = Task.Run(() =>
{
try
{
var data = ep.GetData(filePath);
var statementData = sp.Process(data);
Parallel.ForEach(statementData, item =>
{
StatementModel.RawData.Add(item);
});
}
catch (Exception ex)
{
Dispatcher.Invoke(() =>
{
MessageBox.Show(ex.ToString());
});
}
});
Tasks.Add(t);
}
Task.WhenAll(Tasks)
.ContinueWith(x =>
{
BindData();
ShowProgressBar(false);
});
}
public void Process_ShouldProcessFiles(string fileName)
{
//Given
var processor = new ExcelProcessor(Enumerable.Empty <Validator>());
//When
var config = new API.Models.ExcelConfig
{
SheetName = "one_row",
Fields = new List <FieldConfig>
{
new FieldConfig {
Index = 0
},
new FieldConfig {
Index = 1
},
new FieldConfig {
Index = 2, Type = "numeric"
},
new FieldConfig {
Index = 3
},
}
};
var result = processor.Process(fileName, config);
//Then
var firstItem = result.Json.First();
var name = ((dynamic)firstItem).Name;
double age = ((dynamic)firstItem).Age;
Assert.Equal("Kevin", name);
Assert.Equal(10, age);
result.Errors.Should().BeEmpty();
}
/// <summary>
/// 获取根据blockSize获取DataTable列表
/// </summary>
/// <param name="struc">模版结构</param>
/// <param name="filePath">文件路径</param>
/// <param name="blockSize">每个DataTable最大大小</param>
/// <returns></returns>
public static IList <DataTable> GetDataTableList(ImportTemplateStructure struc, string filePath, int blockSize)
{
if (blockSize <= 0)
{
blockSize = 1000; // 默认一次性处理1000行
}
ImportTemplateCommandNode itcnBegin = struc.DefaultGroup.CommandNodeList.First(tent => tent.CommandCode == ImportTemplateCommandCode.Begin);
ImportTemplateCommandNode itcnEnd = struc.DefaultGroup.CommandNodeList.First(tent => tent.CommandCode == ImportTemplateCommandCode.End);
IList <DataTable> dtList = new List <DataTable>();
DataSet ds = new DataSet();
using (ExcelProcessor processor = ExcelService.GetProcessor(filePath))
{
ds = processor.GetDataSet();
}
DataTable dt = ds.Tables[0];
IList <ImportTemplateColumnNode> ccnodes = struc.DefaultGroup.GetCommonColumnNodeList();
// 设置公用数据列默认值
foreach (ImportTemplateColumnNode tnode in ccnodes)
{
// 行第一个“-1”DataSet从第二行开始,行第二个“-1”，列第一个“-1”数组从0开始
tnode.DefaultValue = dt.Rows[tnode.ValueRowIndex.Value - 2][tnode.ValueColumnIndex.Value - 1];
}
IList <ImportTemplateColumnNode> ocnodes = struc.DefaultGroup.GetOrdinaireColumnNodeList();
int startRowIndex = itcnBegin.RowIndex;
int endRowIndex = itcnEnd.RowIndex;
int startColumnIndex = itcnBegin.ColumnIndex;
int endColumnIndex = itcnEnd.ColumnIndex;
if (itcnBegin.RowIndex == itcnEnd.RowIndex)
{
// DataSet从第二行开始这里需要+1
endRowIndex = dt.Rows.Count + 1;
}
DataTable tdt = null;
for (int i = startRowIndex; i <= endRowIndex; i++)
{
if ((i - startRowIndex) % blockSize == 0)
{
tdt = struc.DefaultGroup.GetDataTableSchema();
dtList.Add(tdt);
}
DataRow drow = tdt.NewRow();
bool emptyflag = true;
for (int j = startColumnIndex; j <= endColumnIndex; j++)
{
ImportTemplateColumnNode tnode = ocnodes.First(tent => (tent.ValueColumnIndex) == j);
if (tnode != null)
{
object tval = dt.Rows[(i - 2)][(j - 1)];
if (tval != null && tval.ToString().Trim() != String.Empty)
{
emptyflag = false;
}
tval = (tval != null ? tval : tnode.DefaultValue);
drow[tnode.ColumnName] = tval;
}
}
// 当前行为空行，这跳过执行
if (emptyflag == true)
{
continue;
}
// 设置公共列值
foreach (ImportTemplateColumnNode tnode in ccnodes)
{
drow[tnode.ColumnName] = tnode.DefaultValue;
}
tdt.Rows.Add(drow);
}
return(dtList);
}
protected void Page_Load(object sender, EventArgs e)
{
if (this.Request.Files.Count > 0)
{
string guid = Guid.NewGuid().ToString();
string filePath = "//WorkTime//InputExcelFiles//" + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(Request.Files[0].FileName);
this.Request.Files[0].SaveAs(Server.MapPath(filePath));
ExcelProcessor ep = ExcelService.GetProcessor(Server.MapPath(filePath));
DataSet ds = ep.GetDataSet();
InputDatas(ds.Tables[0]);
Response.Write("{success:true}");
Response.End();
}
id = RequestData.Get <string>("id", String.Empty);
type = RequestData.Get <string>("type", String.Empty);
SysUser usr = null;
switch (this.RequestAction)
{
case RequestActionEnum.Create:
usr = this.GetPostedData <SysUser>();
usr.DoCreate();
this.SetMessage("新建成功！");
break;
case RequestActionEnum.Update:
usr = this.GetMergedData <SysUser>();
usr.DoUpdate();
this.SetMessage("保存成功！");
break;
case RequestActionEnum.Delete:
usr = this.GetTargetData <SysUser>();
usr.DoDelete();
this.SetMessage("删除成功！");
break;
default:
if (RequestActionString == "clearpass")
{
usr = SysUser.Find(this.RequestData.Get <string>("UserId"));
usr.Password = "";
usr.Remark = "";
usr.Save();
}
else if (RequestActionString == "setpass")
{
MD5Encrypt encrypt = new MD5Encrypt();
SysUser[] users = SysUser.FindAll();
foreach (SysUser user in users)
{
Random rnd = new Random();
int rndNum = rnd.Next(10000000, 99999999);
string encryPassword = encrypt.GetMD5FromString(rndNum.ToString());
user.Password = encryPassword;
user.Remark = rndNum.ToString();
user.Save();
}
}
else if (RequestActionString == "checkkey")
{
if (SysUser.FindAllByProperties("Server_IAGUID", this.RequestData.Get <string>("usbguid")).Length > 0)
{
this.PageState.Add("UserName", SysUser.FindAllByProperties("Server_IAGUID", this.RequestData.Get <string>("usbguid"))[0].Name);
}
else
{
this.PageState.Add("UserName", "");
}
}
else if (RequestActionString == "setkey")
{
SysUser user = SysUser.Find(this.RequestData.Get <string>("userid"));
user.Server_IAGUID = this.RequestData.Get <string>("usbguid");
user.Server_Seed = "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF";
user.ThreeDESKEY = "ABCDEFGhijklmn0123456789";
user.Save();
}
else if (RequestActionString == "sendmail")
{
IList <object> idList = RequestData.GetList <object>("IdList");
SysUser[] tents = SysUser.FindAll(Expression.In("UserID", idList.ToArray()));
foreach (SysUser user in tents)
{
string body = SysParameter.FindAllByProperties("Code", "MailText")[0].Description + "<br>";
body += "您的登录用户名:" + user.LoginName + ";密码:" + user.Remark;
string mailAccount = SysParameter.FindAllByProperties("Code", "MailAccount")[0].Value;
string mailPass = SysParameter.FindAllByProperties("Code", "MailPassword")[0].Value;
string mailServer = SysParameter.FindAllByProperties("Code", "MailSmtpServer")[0].Value;
string mailSenderAddress = SysParameter.FindAllByProperties("Code", "MailSenderAddress")[0].Value;
MailHelper.SendMail(mailSenderAddress, user.Email, "工时分配系统邮件", body, mailAccount, mailPass, mailServer);
}
}
else
{
SearchCriterion.AutoOrder = false;
SearchCriterion.SetOrder(SysUser.Prop_WorkNo);
string dName = SearchCriterion.GetSearchValue <string>("Name");
string workNo = SearchCriterion.GetSearchValue <string>("WorkNo");
SearchCriterion.SetOrder(SysUser.Prop_WorkNo);
if (dName != null && dName.Trim() != "")
{
string where = "select * from SysUser where " + GetPinyinWhereString("Name", dName);
where += " and WorkNo like '%" + workNo + "%'";
this.PageState.Add("UsrList", DataHelper.QueryDictList(where));
}
else
{
users = SysUserRule.FindAll(SearchCriterion);
this.PageState.Add("UsrList", users);
}
}
break;
}
}
private void GetExcelData(string fileName)
{
IList <object[]> data = null;
IList <ExcelProcessor.PictureInfo> pictureInfos = null;
try
{
this._currentExcelProcessor = new ExcelProcessor(fileName);
pictureInfos = this._currentExcelProcessor.GetPictures(0);
data = this._currentExcelProcessor.GetSheetData(0);
if (pictureInfos != null && pictureInfos.Count > 0)
{
foreach (var item in pictureInfos)
{
if (item.Row1 == item.Row2 && item.Col1 == item.Col2)
{
if (item.Row1 < data.Count && item.Col1 < data[item.Row1].Length)
{
data[item.Row1][item.Col1] = item.Picture;
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
DataGridView dataGridView = new DataGridView();
dataGridView.Name = "guidview";
dataGridView.Dock = DockStyle.Fill;
dataGridView.BorderStyle = BorderStyle.None;
dataGridView.AllowUserToAddRows = false;
dataGridView.EditMode = DataGridViewEditMode.EditOnKeystroke;
dataGridView.RowPostPaint += new DataGridViewRowPostPaintEventHandler(dataGridView1_RowPostPaint);
dataGridView.CellMouseDown += new DataGridViewCellMouseEventHandler(dataGridView1_CellMouseDown);
dataGridView.CellValueChanged += new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
dataGridView.RowsAdded += new DataGridViewRowsAddedEventHandler(dataGridView1_RowsAdded);
if (data != null && data.Count > 0)
{
foreach (object item in data[0])
{
string text = item.ToString();
if (ConfigurationManager.AppSettings["imageColumnsName"].Split('|').Contains(text))
{
DataGridViewImageColumn col = new DataGridViewImageColumn();
col.Name = text;
col.HeaderText = text;
col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
col.MinimumWidth = 132;
dataGridView.Columns.Add(col);
}
else
{
DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
col.HeaderText = text;
col.Name = text;
col.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dataGridView.Columns.Add(col);
}
}
data.RemoveAt(0);
if (data.Count > 0)
{
foreach (object[] row in data)
{
dataGridView.Rows.Add(row);
}
}
}
TabPage tabPage = new TabPage(Path.GetFileNameWithoutExtension(fileName));
tabPage.Tag = new TabPageTag(fileName, false, this._currentExcelProcessor);
tabPage.Controls.Add(dataGridView);
_tabControl1.Controls.Add(tabPage);
_tabControl1.SelectedTab = tabPage;
this._currentDataGridView = dataGridView;
}
protected void Page_Load(object sender, EventArgs e)
{
if (this.Request.Files.Count > 0)
{
string guid = Guid.NewGuid().ToString();
string filePath = "//WorkTime//InputExcelFiles//" + Guid.NewGuid().ToString() + System.IO.Path.GetExtension(Request.Files[0].FileName);
this.Request.Files[0].SaveAs(Server.MapPath(filePath));
ExcelProcessor ep = ExcelService.GetProcessor(Server.MapPath(filePath));
DataSet ds = ep.GetDataSet();
string exMessage = "";
workNumbers = "";
try
{
InputDatasV2(ds.Tables[0]);
}
catch (Exception ex)
{
exMessage = ex.Message;
}
if (workNumbers != "")
{
Response.Write("{success:false,message:'" + workNumbers + "'}");
Response.End();
}
else
{
Response.Write("{success:true}");
Response.End();
}
}
id = RequestData.Get <string>("id", String.Empty);
type = RequestData.Get <string>("type", String.Empty);
SysUser usr = null;
switch (this.RequestAction)
{
case RequestActionEnum.Create:
usr = this.GetPostedData <SysUser>();
usr.DoCreate();
this.SetMessage("新建成功！");
break;
case RequestActionEnum.Update:
usr = this.GetMergedData <SysUser>();
usr.DoUpdate();
this.SetMessage("保存成功！");
break;
case RequestActionEnum.Delete:
usr = this.GetTargetData <SysUser>();
usr.DoDelete();
this.SetMessage("删除成功！");
break;
default:
if (RequestActionString == "clearpass")
{
usr = SysUser.Find(this.RequestData.Get <string>("UserId"));
usr.Password = "";
usr.Save();
}
else
{
SearchCriterion.AutoOrder = false;
SearchCriterion.SetOrder(SysUser.Prop_WorkNo);
string dName = SearchCriterion.GetSearchValue <string>("Name");
if (dName != null && dName.Trim() != "")
{
string where = "select * from SysUser where " + GetPinyinWhereString("Name", dName);
this.PageState.Add("UsrList", DataHelper.QueryDictList(where));
}
else
{
users = SysUserRule.FindAll(SearchCriterion);
this.PageState.Add("UsrList", users);
}
}
break;
}
}
public ExcelController(ExcelProcessor processor)
{
this.processor = processor;
}
public TabPageTag(string fileName, bool unSaved, ExcelProcessor excelProcessor)
{
this.FileName = fileName;
this.UnSaved = unSaved;
this.ExcelProcessor = excelProcessor;
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ExportToExcelBt_Click_1(object sender, EventArgs e)
{
ExcelProcessor.GenerateExcelFile().AddDataToFile(ScheduleEngineersDataGridView);
}
private void SaveToExcel(ExcelProcessor excelProcessor, DataGridView grid)
{
string dicPath = $"{AppDomain.CurrentDomain.BaseDirectory}\\Backup";
if (!Directory.Exists(dicPath))
{
Directory.CreateDirectory(dicPath);
}
string fileName = $"{Path.GetFileNameWithoutExtension(excelProcessor.FilePath)}_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}{Path.GetExtension(excelProcessor.FilePath)}";
string filePath = $"{dicPath}\\{fileName}";
try
{
File.Copy(excelProcessor.FilePath, filePath);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
for (int row = 0; row < grid.Rows.Count; row++)
{
for (int col = 0; col < grid.Rows[row].Cells.Count; col++)
{
DataGridViewCell cell = grid.Rows[row].Cells[col];
if (grid.Columns[cell.ColumnIndex] is DataGridViewImageColumn)
{
byte[] temp = null;
if (cell.Value is Bitmap)
{
temp = ImageProcessor.BitmapToBytes(cell.Value as Bitmap);
}
else if (cell.Value is byte[])
{
temp = cell.Value as byte[];
}
else
{
continue;
}
excelProcessor.RemovePictureInCell(row + 1, col, 0);
excelProcessor.InsertPictureInCell(col, row + 1, col + 1, row + 2, 5, 5, 5, 5, temp, 0);
if (temp != null)
{
Bitmap bitmap = ImageProcessor.BytesToBitmap(temp);
excelProcessor.SetCellWidth(col, bitmap.Width, 0);
excelProcessor.SetRowHeight(row + 1, bitmap.Height, 0);
}
}
else if (grid.Columns[cell.ColumnIndex] is DataGridViewTextBoxColumn)
{
cell.Value = cell.Value ?? string.Empty;
excelProcessor.SetCellValue(cell.Value.ToString(), row + 1, col, 0);
}
}
}
excelProcessor.Save();
}
private InputProcessor InitProcessor()
{
var inputFile = new FileInfo(InputFileName);
InputProcessor processor = null;
switch (inputFile.Extension)
{
case ".xls":
case ".xlsx":
processor = new ExcelProcessor(inputFile);
break;
case ".csv":
case ".txt":
processor = new TextProcessor(inputFile);
break;
default:
return(null);
}
return(processor);
}
