internal static void RearrangeWorksheets(ExcelWorksheets worksheets, int sourcePositionId, int targetPositionId, bool before)
{
if (sourcePositionId == targetPositionId)
{
return;
}
lock (worksheets)
{
var sourceSheet = worksheets[sourcePositionId];
var targetSheet = worksheets[targetPositionId];
var index = targetSheet._package._worksheetAdd;
worksheets._worksheets.Move(sourcePositionId - index, targetPositionId - index, before);
var from = Math.Min(sourcePositionId, targetPositionId);
var to = Math.Max(sourcePositionId, targetPositionId);
for (int i = from; i <= to; i++)
{
worksheets[i].PositionId = i;
}
MoveTargetXml(worksheets, sourceSheet, targetSheet, before);
}
}
public int readExcel(String fileName)
{
int workSheets = 0;
try
{
ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(fileName));
ExcelWorksheets wrkSheets = excel.Workbook.Worksheets;
foreach (ExcelWorksheet wrkSheet in wrkSheets)
{
for (int i = wrkSheet.Dimension.Start.Row; i <= wrkSheet.Dimension.End.Row; i++)
{
for (int j = wrkSheet.Dimension.Start.Column; j <= wrkSheet.Dimension.End.Column; j++)
{
object cellValue = wrkSheet.Cells[j, i].Value;
Console.Write(" " + cellValue.ToString() + " ");
}
Console.WriteLine("");
}
}
}
catch (Exception ex)
{
}
return(workSheets);
}
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="FileName">文件名</param>
/// <param name="WhichTable">第几张表</param>
/// <param name="Whichline">第几行</param>
/// <param name="HowManyColumns">第几列</param>
public static string ReadExcel(string FileName, int WhichTable, int Whichline, int HowManyColumns)
{
string path = Application.streamingAssetsPath + "/Excel/" + FileName + ".xlsx";
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (ExcelPackage excel = new ExcelPackage(fs))
{
ExcelWorksheets workSheets = excel.Workbook.Worksheets;
if (!(WhichTable >= 1 && WhichTable <= workSheets.Count))
{
Debug.LogErrorFormat("工作表不存在 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns);
}
ExcelWorksheet workSheet = workSheets[WhichTable];
int rowCount = workSheet.Dimension.End.Row;
int colCount = workSheet.Dimension.End.Column;
if (!(HowManyColumns >= 1 && HowManyColumns <= colCount))
{
Debug.LogErrorFormat("列越界 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns);
}
if (!(Whichline >= 1 && Whichline <= rowCount))
{
Debug.LogErrorFormat("行越界 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns);
}
var text = workSheet.Cells[Whichline, HowManyColumns].Text ?? "";
return(text);
}
}
}
private void FormatAddress(FileInfo f)
{
using (ExcelPackage ep = new ExcelPackage(f))
{
ExcelWorkbook ewb = ep.Workbook;
ExcelWorksheets ews = ewb.Worksheets;
foreach (var _ews in ews)
{
if (_ews.Name == "Address" || _ews.Name == "Mailing Address")
{
SetColumnHeader(_ews);
char col = 'B';
for (int i = 0; i < _ews.Dimension.Rows - 1; i++)
{
string fullAddress = _ews.Cells[$"{ col }{ i + 2 }"].Text;
var splitAddress = SplitAddress(fullAddress);
foreach (var a in splitAddress)
{
_ews.Cells[$"{ col }{ i + 2 }"].Value = a;
col++;
}
col = 'B';
}
ep.Save();
}
}
}
}
public async Task <int> UploadFileAsync(string fileName, string pathTmp)
{
try
{
FileInfo file = new FileInfo(pathTmp);
ExcelPackage excel = new ExcelPackage(file);
ExcelWorksheets workSheets = excel.Workbook.Worksheets;
var fileId = Guid.NewGuid().ToString();
_unit.RepFile.Create(new Data.Dto.File
{
Id = fileId,
UploadDateTime = DateTime.UtcNow,
Name = fileName,
NameTmp = pathTmp
});
foreach (ExcelWorksheet workSheet in workSheets)
{
var workSheetId = Guid.NewGuid().ToString();
_unit.RepWorkSheet.Create(new Data.Dto.WorkSheet
{
Id = workSheetId,
FileId = fileId,
Number = workSheet.Index,
Name = workSheet.Name
});
for (var rowNumber = 1; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
{
var rowId = Guid.NewGuid().ToString();
_unit.RepRow.Create(new Data.Dto.Row
{
Id = rowId,
WorkSheetId = workSheetId,
Number = rowNumber,
});
for (int cellNumber = 1; cellNumber <= workSheet.Dimension.End.Column; cellNumber++)
{
string cellValue = workSheet.Cells[rowNumber, cellNumber].Value.ToString();
_unit.RepCol.Create(new Data.Dto.Col
{
Id = Guid.NewGuid().ToString(),
RowId = rowId,
Name = $"col{cellNumber}",
Value = cellValue
});
}
}
}
return(await _unit.CommitAsync());
}
catch (Exception ex)
{
throw new Exception("Не удалось сохранить файл В БД", ex);
}
}
public JsonResult UploadFile()
{
DtoRisk dto = new DtoRisk();
if (Request.Files.Count > 0)
{
try
{
object[,] obj = null;
int noOfCol = 0;
int noOfRow = 0;
HttpFileCollectionBase file = Request.Files;
if ((file != null) && (file.Count > 0))
{
byte[] fileBytes = new byte[Request.ContentLength];
int data = Request.InputStream.Read(fileBytes, 0, Convert.ToInt32(Request.ContentLength));
using (ExcelPackage package = new ExcelPackage(Request.InputStream))
{
ExcelWorksheets worksheets = package.Workbook.Worksheets;
ExcelWorksheet workSheet = worksheets.Single(x => x.Name.Equals(dto.WorksheetName));
noOfCol = workSheet.Dimension.End.Column;
noOfRow = workSheet.Dimension.End.Row;
obj = new object[noOfRow, noOfCol];
obj = (object[, ])workSheet.Cells.Value;
}
}
return(Json(new { data = obj, row = noOfRow, col = noOfCol }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
}
}
return(Json("", JsonRequestBehavior.AllowGet));
}
static void LoadExcel()
{
string path = Application.dataPath + "/Excel/test.xlsx";
//读取Excel文件
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
using (ExcelPackage excel = new ExcelPackage(fs)) {
ExcelWorksheets workSheets = excel.Workbook.Worksheets;
//遍历所有worksheets
for (int i = 1; i <= workSheets.Count; i++)
{
ExcelWorksheet workSheet = workSheets [i];
int colCount = workSheet.Dimension.End.Column;
//获取当前sheet名
Debug.LogFormat("Sheet {0}", workSheet.Name);
for (int row = 1, count = workSheet.Dimension.End.Row; row <= count; row++)
{
for (int col = 1; col <= colCount; col++)
{
//读取每个格子中的数据
var text = workSheet.Cells [row, col].Text ?? "";
Debug.LogFormat("下标:{0},{1} 内容:{2}", row, col, text);
}
}
}
}
}
}
public void LoadFile(string filePath)
{
FileInfo file = new FileInfo(filePath);
ExcelPackage pkg = new ExcelPackage(file);
worksheets = pkg.Workbook.Worksheets;
}
/// <summary>
/// 新しい空のワークシートを追加します。
/// </summary>
/// <param name="worksheets">ワークシートコレクション</param>
/// <param name="sheetName">ワークシートの名前</param>
/// <param name="fontName">フォント名</param>
/// <returns>ワークシート</returns>
public static ExcelWorksheet Add(this ExcelWorksheets worksheets, string sheetName, string fontName)
{
var worksheet = worksheets.Add(sheetName);
worksheet.Cells.Style.Font.Name = fontName;
return(worksheet);
}
public List <Email> ReadExcelData(string FilePath)
{
Email _email;
List <Email> _myemaillist = new List <Email>();
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
//get the first worksheet in the workbook
ExcelWorksheets worksheet = package.Workbook.Worksheets;
foreach (ExcelWorksheet sheet in worksheet)
{
int colCount = sheet.Dimension.End.Column; //get Column Count
int rowCount = sheet.Dimension.End.Row; //get row count
for (int row = 2; row <= rowCount; row++)
{
if (sheet.Cells[row, 1].Value != null)
{
if (sheet.Cells[row, 1].Value.ToString().Trim().IndexOf("@") > 0)
{
_email = new Email();
_email.EmailAddress = sheet.Cells[row, 1].Value.ToString().Trim();
_email.Id = _myemaillist.Count + 1;
_email.VerificationStatusId = Enum.VerificationStatus.Pending;
_email.Verification = Enum.VerificationStatus.Pending.ToString();
_myemaillist.Add(_email);
}
}
}
}
}
return(_myemaillist);
}
private DataTable ReadExcel(string path, bool hasHeader = true)
{
ExcelPackage package = new ExcelPackage(new FileInfo(path));
ExcelWorkbook wb = package.Workbook;
ExcelWorksheets wsList = wb.Worksheets;
ExcelWorksheet ws = wsList[1];
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
return(tbl);
}
public ExcelSheet Parse(String path)
{
// path to your excel file
FileInfo fileInfo = new FileInfo(path);
ExcelPackage package = new ExcelPackage(fileInfo);
ExcelWorksheets worksheets = package.Workbook.Worksheets;
ExcelSheet excelSheet = new ExcelSheet();
for (int i = 0; i < worksheets.Count; i++)
{
string sheetName = worksheets[i].Name;
PropertyInfo prop = excelSheet.GetType().GetProperty(sheetName);
Type type = prop.PropertyType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
{
var newcollection = worksheets[i].ConvertSheetToObjects(type);
prop.SetValue(excelSheet, newcollection, null);
}
else
{
var obj = worksheets[i].ConvertSheetToObject(type);
prop.SetValue(excelSheet, obj, null);
}
}
return(excelSheet);
}
private static ExcelWorksheet getSheet(ExcelPackage package, String sheetName)
{
Console.WriteLine("Enter getSheet(): package={0}, sheet={1}\n", package, sheetName);
if (package == null || sheetName == null)
{
Console.WriteLine("Input Error!\n");
return(null);
}
ExcelWorksheets sheets = package.Workbook.Worksheets;
ExcelWorksheet wantedSheet = null;
foreach (ExcelWorksheet sheet in sheets)
{
if (sheet.Name.Equals(sheetName))
{
Console.WriteLine("{0} is found!\n", sheetName);
wantedSheet = sheet;
}
}
if (wantedSheet == null)
{
Console.WriteLine("NO SHEET FOUND !!!\n");
}
Console.WriteLine("Exit getSheet()\n");
return(wantedSheet);
}
public virtual ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle)
{
var spreadSheet = excelWorksheets.Add(SpreadsheetName);
var dataTable = ConvertToDataTables().First();
foreach (var column in dataTable.Columns)
{
var columnName = column.ToString();
var friendlyName = column.ToString()
.ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true);
dataTable.Columns[column.ToString()].ColumnName = friendlyName;
}
spreadSheet.Cells[1, 1].LoadFromDataTable(dataTable, true, tableStyle);
spreadSheet.Cells.AutoFitColumns();
//No rows of close approach data were added and the spreadsheet is empty, no need to include it.
if (!spreadSheet.Cells.Any())
{
excelWorksheets.Delete(spreadSheet);
return(null);
}
return(spreadSheet);
}
void ReadExcel(string path, List <ComboBoxItem> list)
{
FileInfo file = new FileInfo(path);
//检测文件是否正在被使用
try {
using (FileStream fs = file.Open(FileMode.Open, FileAccess.Read)) {
fs.Close();
}
}
catch (IOException) {
string errorText = string.Format("数据表{0}正在被使用，请将其关闭后再打开该软件。", file.Name);
MessageBox.Show(errorText, "Error花生", MessageBoxButtons.OK, MessageBoxIcon.Error);
Environment.Exit(1);
}
ExcelPackage package = new ExcelPackage(file);
ExcelWorksheets sheets = package.Workbook.Worksheets;
foreach (var sheet in sheets)
{
int rows = sheet.Dimension.Rows;
list.Add(new ComboBoxItem("", string.Format("-------- {0} --------", sheet.Name), false));
for (int row = 2; row < rows; row++)
{
if (sheet.Cells[rows, 1, rows, 2].All(c => c.Value != null))
{
list.Add(new ComboBoxItem(sheet.GetValue(row, 2).ToString(), sheet.GetValue(row, 1).ToString(), true));
}
}
}
}
private async Task AppendSheetForEachSchool(ExcelWorksheets worksheets, School school)
{
var worksheet = worksheets.Add(school.Name);
worksheet.AppendHeader(typeof(ActivityExportHeader), 1);
IEnumerable <Entities.Class> efClasses = await this._unitOfWork.Classes.GetWithSchoolId(school.Id);
List <Guid> classIds = efClasses.Select(c => c.Id).ToList();
IEnumerable <Entities.Track> efTracks = await this._unitOfWork.Tracks.GetFullInformationByIds(classIds);
IEnumerable <Track> tracks = _mapper.Map <IEnumerable <Entities.Track>, IEnumerable <Track> >(efTracks);
int rowIndex = 1;
foreach (var track in tracks)
{
rowIndex++;
worksheet.Cells[rowIndex, ActivityExportHeader.ChildName.ColumnIndex].Value = track.ChildName;
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckIn.ColumnIndex].Value = track.TimeCheckIn.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckOut.ColumnIndex].Value = track.TimeCheckOut.ToLocalTime();
worksheet.Cells[rowIndex, ActivityExportHeader.ClassName.ColumnIndex].Value = track.Class.Name;
worksheet.Cells[rowIndex, ActivityExportHeader.TeacherName.ColumnIndex].Value = track.Class.Teacher.Name;
}
worksheet.AutoFixColumns(typeof(ActivityExportHeader));
}
private void ExcelToJson(string excelName)
{
if (!string.IsNullOrEmpty(excelName))
{
string filepath = Application.dataPath + "/Resources/Excel/" + excelName;
string headPath = $"{Application.dataPath}/{txt_JsonSavePath}";
using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
using (ExcelPackage ep = new ExcelPackage(fs))
{
//获得所有工作表
ExcelWorksheets workSheets = ep.Workbook.Worksheets;
List <System.Object> lst = new List <object>();
//遍历所有工作表
for (int i = 1; i <= workSheets.Count; i++)
{
//当前工作表
ExcelWorksheet sheet = workSheets[i];
//初始化集合
lst.Clear();
int columnCount = sheet.Dimension.End.Column;
int rowCount = sheet.Dimension.End.Row;
//根据实体类创建对象集合序列化到json中
for (int z = 4; z <= rowCount; z++)
{
Assembly ab = Assembly.Load("Assembly-CSharp"); //要注意对面在那个程序集里面dll
Type type = ab.GetType($"Table.{sheet.Name}");
if (type == null)
{
Debug.LogError("你还没有创建对应的实体类!");
return;
}
if (!Directory.Exists(headPath))
{
Directory.CreateDirectory(headPath);
}
object o = ab.CreateInstance(type.ToString());
for (int j = 1; j <= columnCount; j++)
{
FieldInfo fieldInfo = type.GetField(sheet.Cells[i, j].Text); //先获得字段信息，方便获得字段类型
object value = Convert.ChangeType(sheet.Cells[z, j].Text, fieldInfo.FieldType);
type.GetField(sheet.Cells[1, j].Text).SetValue(o, value);
}
lst.Add(o);
}
//写入json文件
string jsonPath = $"{headPath}/{sheet.Name}.json";
if (!File.Exists(jsonPath))
{
File.Create(jsonPath).Dispose();
}
File.WriteAllText(jsonPath, JsonConvert.SerializeObject(lst));
}
}
}
AssetDatabase.Refresh();
}
}
public ExcelWorksheet CreateAWorksheetFromTemplate(ExcelWorkbook current, string name)
{
ExcelWorksheets _sheets = LoadTemplates();
current.Worksheets.Add(name, _sheets[templateWorksheetName]);
_sheets.Dispose();
return(current.Worksheets[name]);
}
public static ExcelWorksheet ReadExcelSheet(string excelFloder, string sheetName)
{
if (mExcelSheetCaches == null)
{
mExcelSheetCaches = new Dictionary <string, ExcelWorksheet>();
}
ExcelWorksheet sheet;
if (!mExcelSheetCaches.TryGetValue(sheetName, out sheet))
{
Dictionary <string, string> nameToPath = new Dictionary <string, string>();
var configSheetTPaths = File.ReadAllText(Path.Combine(excelFloder, NAME_TO_PATH));
var sheetToPaths = configSheetTPaths.Split(',');
foreach (var sheetToPath in sheetToPaths)
{
if (string.IsNullOrEmpty(sheetToPath))
{
continue;
}
var sheetAndPath = sheetToPath.Split('=');
if (sheetAndPath != null && sheetAndPath.Length == 2)
{
nameToPath.Add(sheetAndPath[0], sheetAndPath[1]);
}
}
string excelName = string.Empty;
if (nameToPath.TryGetValue(sheetName, out excelName))
{
var dirInfo = Directory.CreateDirectory(excelFloder);
FileInfo[] fileInfos = dirInfo.GetFiles();
for (int i = 0; i < fileInfos.Length; i++)
{
FileInfo fileInfo = fileInfos[i];
if (fileInfo.Name == excelName)
{
ExcelPackage ep = new ExcelPackage(fileInfo);
ExcelWorksheets worksheets = ep.Workbook.Worksheets;
for (int j = 1; j <= worksheets.Count; j++)
{
ExcelWorksheet newSheet = worksheets[j];
mExcelSheetCaches.Add(newSheet.Name, newSheet);
if (newSheet.Name == sheetName)
{
sheet = newSheet;
}
}
}
}
}
else
{
UnityEngine.Debug.LogErrorFormat("新增了配置表【{0}】， 请用菜单工具生成一下映射。", sheetName);
}
}
return(sheet);
}
void Start()
{
instance = this;
cards = new List <Card>();
teamA = new Team();
teamB = new Team();
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists)
{
ExcelPackage excel = new ExcelPackage(newFile);
ExcelWorksheets sheets = excel.Workbook.Worksheets;
LoadDefineValue(sheets["define"]);
InitAllCards(sheets["card"]);
Print(System.DateTime.Now.TimeOfDay.ToString(), 1);
for (int tier = 1; tier < 30 && cards.Count > 0; tier++)
{
foreach (Card c in cards)
{
c.chart.per = 0f;
c.chart.all = 0;
c.chart.win = 0;
}
for (int i = 0; i < 10000; i++)
{
InitTeam();
Team teamwin = Battle(teamA, teamB);
AddData(teamwin);
}
foreach (Card c in cards)
{
c.chart.per = ((float)c.chart.win) / c.chart.all;
}
List <Card> sortedList = cards.OrderByDescending(c => (1f - c.chart.per)).ToList();
cards = sortedList;
int nRemove = Math.Min(4, cards.Count);
for (int i = 0; i < nRemove; i++)
{
if (tier > 16)
{
sheets["card"].Cells[cards[0].id + 2, 19].Value = string.Format("Tier " + (tier - 16));
}
else
{
sheets["card"].Cells[cards[0].id + 2, 19].Value = "";
}
sheets["card"].Cells[cards[0].id + 2, 16].Value = cards[0].chart.win;
sheets["card"].Cells[cards[0].id + 2, 17].Value = cards[0].chart.all;
cards.Remove(cards[0]);
}
}
Print(System.DateTime.Now.TimeOfDay.ToString(), 1);
SaveData(sheets["card"]);
excel.Save();
}
}
public static void GenerateWorksheet(ExcelWorksheets worksheets, DataTable dataTable, string strWorksheetName)
{
try
{
ExcelWorksheet ws;
if (worksheets.Count(w => w.Name == strWorksheetName) == 0)
{
ws = worksheets.Add(strWorksheetName);
Log.LogMessage("Adding new Worksheet: " + strWorksheetName);
}
else
{
ws = worksheets[strWorksheetName];
ws.Cells.Clear();
Log.LogMessage("Clearing Values for Worksheet: " + strWorksheetName);
}
int cols = ws.Dimension?.Columns ?? dataTable.Columns.Count;
object[] columns = dataTable.Rows[0].ItemArray;
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
for (int x = 1; x <= columns.Count(); x++)
{
if (Config.Dates.Contains(ws.Cells[1, x].Value))
{
ws.Column(x).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
}
Log.LogMessage("Added data to Worksheet: " + strWorksheetName);
// format header cells
using (var range = ws.Cells[1, 1, 1, cols])
{
Log.LogMessage("Formating Header Cells for Worksheet: " + strWorksheetName);
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
// Create autofilter for the range of cells
ws.Cells[ws.Dimension?.Address].AutoFilter = true;
// Autofit columns for all cells
ws.Cells[ws.Dimension?.Address].AutoFitColumns();
Log.LogMessage("Created " + strWorksheetName + " Worksheet");
}
catch (Exception ex)
{
Log.LogMessage("Error Creating " + strWorksheetName + ", Error: " + ex.Message);
}
}
public static ExcelWorksheet CopyWithoutContent(this ExcelWorksheets sheets, string name, string newName)
{
var sheet = sheets.Copy(name, newName);
var dimension = sheet.Dimension;
sheet.Cells[dimension.Start.Row, dimension.Start.Column, dimension.End.Row, dimension.End.Column].Value = null;
return(sheet);
}
public void SetUp()
{
if (File.Exists(Filename))
{
File.Delete(Filename);
}
package = new ExcelPackage(new FileInfo(Filename));
doc = package.Workbook.Worksheets;
}
private static void WriteChange(ExcelWorksheet newSheet)
{
ReadExcel(delegate(ExcelPackage package)
{
ExcelWorksheets sheets = package.Workbook.Worksheets;
ExcelWorksheet sheet = sheets["Sheet1"];
int rowsCount = 26;
int m_old_typeIndex = 0;
for (int i = 1; i < rowsCount; i++)
{
object obj = sheet.Cells[1, i].Value;
if (obj != null)
{
string tempValue = obj.ToString();
if (tempValue.Equals("子类型大小(MB)"))
{
m_old_typeIndex = i;
}
}
}
for (int i = 0; i < typeList.Count; i++)
{
if ((typeList[i].Equals("apk") || typeList[i].Equals("ab")))
{
if (newSheet.Cells[i + 1, oldIndex].Value != null)
{
int iso_index = int.Parse(newSheet.Cells[i + 1, oldIndex].Value.ToString());
if (sheet.Cells[iso_index, m_old_typeIndex].Value != null)
{
float old_value = float.Parse(sheet.Cells[iso_index, m_old_typeIndex].Value.ToString());
float new_value = float.Parse(newSheet.Cells[i + 1, childSizeTypeIndex].Value.ToString());
if (old_value > new_value)
{
newSheet.Cells[i + 1, changeIndex].Value = "下降" + Math.Abs(new_value - old_value);
//ExcelColor ec = new ExcelColor();
//newSheet.Cells[i + 1, changeIndex].Style.
}
else if (old_value < new_value)
{
newSheet.Cells[i + 1, changeIndex].Value = "上升" + Math.Abs(new_value - old_value);
}
else
{
newSheet.Cells[i + 1, changeIndex].Value = "无上升下降";
}
UnityEngine.Debug.Log("iso_index:" + iso_index + " old_value:" + old_value + " new_value:" + new_value);
}
}
}
}
package.Save();
}, @"E:\wendang文档\炫舞手游_2017年12月份版本\资源总量分析\资源总量分析_2017_10_23.xlsx");
}
/// <summary>
/// Add a new sheet to the collection. Replace if already exists.
/// </summary>
/// <param name="sheets"></param>
/// <param name="name"></param>
/// <returns></returns>
public static ExcelWorksheet AddOrReplace(this ExcelWorksheets sheets, string name)
{
if (sheets.Any(x => StringComparer.OrdinalIgnoreCase.Equals(x.Name, name)))
{
sheets.Delete(name);
}
return(sheets.Add(name));
}
private static void SetAnoMes(ExcelWorksheets wss, List <LancamentoViewModel> lancamentos)
{
var mes = $"{(DateTime)lancamentos.First().Data:MMM}".ToUpper();
var ano = $"{(DateTime)lancamentos.First().Data:yyyy}";
(wss["Resumo"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}";
(wss["Despesas"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}";
(wss["Receitas"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}";
}
private void SetChineseExcel(string path, Action <List <object> > action, List <object> arg)
{
using (ExcelPackage sourcePackage = new ExcelPackage(new FileInfo(path)))
{
//统计所有表中的中文，放入字典中
ExcelWorksheets sheets = sourcePackage.Workbook.Worksheets;
foreach (ExcelWorksheet sheet in sheets)
{
//排除两个sheet
if (sheet.Name == "列表" || sheet.Name == "样例" || sheet.Name == "MasterSystemVariable")
{
continue;
}
int rows = sheet.Dimension.Rows;
int cols = sheet.Dimension.Columns;
for (int j = 1; j <= cols; j++)
{
if (sheet.Cells[3, j].Value == null)
{
continue;
}
//第三行是值类型
string typeStr = sheet.Cells[3, j].Value.ToString();
//如果不是字符串则不作处理
if (!typeStr.StartsWith("varchar") && !typeStr.StartsWith("nvarchar"))
{
continue;
}
//第4行开始为正式数据
for (int i = 4; i <= rows; i++)
{
//如果第一行就为空，则代表后面没数据了，直接break
if (sheet.Cells[i, 1].Value == null)
{
break;
}
//如果不是第一行为空，就continue
if (sheet.Cells[i, j].Value == null)
{
continue;
}
if (action != null)
{
List <object> temp = new List <object>();
temp.AddRange(arg);
temp.Add(sheet);
temp.Add(i);
temp.Add(j);
action(temp);
}
}
}
//break;
}
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheets"></param>
/// <param name="name"></param>
/// <param name="list"></param>
/// <param name="tableStyles"></param>
public static void AddDataToWorkSheet <T>(this ExcelWorksheets worksheets, String name, IList <T> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8)
{
ExcelWorksheet worksheet = worksheets.Add(name);
var dataRange = worksheet.Cells["A1"].LoadFromCollection(
list,
true, tableStyles);
dataRange.AutoFitColumns();
return;
}
public ExcelWorksheetsInstance(ObjectInstance prototype, ExcelWorksheets excelWorksheets)
: this(prototype)
{
if (excelWorksheets == null)
{
throw new ArgumentNullException("excelWorksheets");
}
m_excelWorksheets = excelWorksheets;
}
public static void readAbPath(ExcelPackage package)
{
ExcelWorksheets sheets = package.Workbook.Worksheets;
ExcelWorksheet sheet = sheets["Sheet1"];
ReadTypeAndPath(sheet, typeList, pathList);
WritePIInfo(package, sheet);
UnityEngine.Debug.Log("typeList:" + typeList.Count + " pathList:" + pathList.Count);
}
public Workbook()
{
_Properties = new DocumentProperties();
_ExcelWorkbook = new ExcelWorkbookDetails();
_SheetStyles = new SheetStyles();
_Worksheets = new ExcelWorksheets();
_Names = new NamedRanges();
}
