public void init_data(string v_pmkey = "id", int v_row_begin = 3)
{
m_src_infos = new Dictionary <CellPoint, List <object> >();
Excel.Cells data = m_sheet.Cells;
int col = m_header.get_col(v_pmkey);
if (col < 0)
{
ZFDebug.Error(string.Format("{0}中没找到名为{1}的列", m_sheet_name, v_pmkey));
return;
}
pm_index = new Dictionary <string, int>();
for (int i = v_row_begin; i < 100000; i++)
{
object test_obj = data[i, col].Value;
if (test_obj == null)
{
break;
}
if (!pm_index.ContainsKey(test_obj.ToString()))
{
pm_index.Add(test_obj.ToString(), i);
}
}
}
/// <summary>
/// 自己定义的
/// </summary>
public static bool Ecu_Id_DataTableToExcel(DataTable datatable, string filepath, out string error)
{
//string file = Application.StartupPath + "\\13id\\" +"id.xls";
//filepath = file;
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells.SetColumnWidth(4, 40);
cells.SetColumnWidth(3, 40);
Aspose.Cells.Style style = new Style();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
StyleFlag a = new StyleFlag();
a.WrapText = true;
style.IsTextWrapped = true; //文本换行 这里很重要的；有助于格式的的显示内容
cells.ApplyStyle(style, a);
cells.ImportDataTable(datatable, true, "A5");
workbook.Save(filepath);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
return(true);
}
/// <summary>
/// 无须电脑安装Excel，读取Excel文件
/// </summary>
/// <param name="excelPath"></param>
/// <returns></returns>
public static System.Data.DataTable GetExcelDataTableByAspose(string excelPath)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(excelPath);
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
System.Data.DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxRow + 1, cells.MaxColumn + 1, true);
return(dt);
}
public void init(Excel.Cells v_data, int v_row, SheetHeader v_header)
{
sheetName = v_header.getData(v_data, v_row, "sheet名") as string;
//optFileName = v_header.getData(v_data, v_row, "导出文件") as string;
dataOffX = Convert.ToInt32(v_header.getData(v_data, v_row, "数据偏移X"));
dataOffY = Convert.ToInt32(v_header.getData(v_data, v_row, "数据偏移Y"));
optCliFileName = v_header.getData(v_data, v_row, "导出客户端文件") as string;
optCliLanguage = getLuaguage(optCliFileName);
optSrvFileName = v_header.getData(v_data, v_row, "导出服务端文件") as string;
optSrvLanguage = getLuaguage(optSrvFileName);
pmKey = _getPmKey(v_header.getData(v_data, v_row, "主键") as string);
string shieldColNames = v_header.getData(v_data, v_row, "屏蔽字段") as string;
if (string.IsNullOrEmpty(shieldColNames))
{
shildKeys = new string[0];
}
else
{
shildKeys = (v_header.getData(v_data, v_row, "屏蔽字段") as string).Split(',', '，');
}
isOpt = readBool(v_header, v_data, v_row, "是否导出");
isDataPersistence = readBool(v_header, v_data, v_row, "常驻内存");
isSingleKey = readBool(v_header, v_data, v_row, "SingleKey");
note = v_header.getData(v_data, v_row, "表注释") as string;
}
public bool readData(Excel.Cells v_data, int v_row, SheetHeader v_header)
{
try
{
int fieldNameIdx = v_header["字段名"];
int tableNameIdx = v_header["表名"];
int sheetNameIdx = v_header["sheet名"];
int columIndexIdx = v_header["列行数"];
int colimNameIdx = v_header["列名"];
int noteColNameIdx = v_header["备注名"];
int dataRowBeginIdx = v_header["起始行"];
int valueTypeIdx = v_header["值类型"];
fieldName = v_data[v_row, fieldNameIdx].StringValue;
excelFileName = v_data[v_row, tableNameIdx].StringValue;
sheetName = v_data[v_row, sheetNameIdx].StringValue;
headRow = v_data[v_row, columIndexIdx].IntValue;
columName = v_data[v_row, colimNameIdx].StringValue;
noteColName = v_data[v_row, noteColNameIdx].StringValue;
dataRowBegin = v_data[v_row, dataRowBeginIdx].IntValue;
valueType = v_data[v_row, valueTypeIdx].StringValue;
}
catch (Exception ex)
{
Debug.Error(ex.ToString());
return(false);
}
return(true);
}
public void init_data(string v_pmkey = "id", int v_row_begin = 3)
{
pm_index = new Dictionary <string, _PmLoc>();
for (int i = 0; i < _sheets.Count; i++)
{
Excel.Worksheet theSheet = _sheets[i].Sheet;
ExcelHeader theHeader = _sheets[i].Header;
//m_src_infos = new Dictionary<CellPoint, List<object>>();
Excel.Cells data = theSheet.Cells;
int col = theHeader.get_col(v_pmkey);
if (col < 0)
{
ZFDebug.Error(string.Format("{0}中没找到名为{1}的列", theSheet.Name, v_pmkey));
return;
}
for (int row = v_row_begin; row < 100000; row++)
{
object test_obj = data[row, col].Value;
if (test_obj == null)
{
break;
}
if (!pm_index.ContainsKey(test_obj.ToString()))
{
pm_index.Add(test_obj.ToString(), new _PmLoc(row, i));
}
}
}
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create Workbook Object
Workbook wb = new Workbook();
// Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
Cell c5 = ws.Cells["C5"];
// Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;
// Increase the width of the column C
cell.SetColumnWidth(c5.Column, 30);
// Add Text to the Firts Cell with Explicit Line Breaks
c5.PutValue("I am using\nthe latest version of \nAspose.Cells to \ntest this functionality");
// Make Cell's Text wrap
Style style = c5.GetStyle();
style.IsTextWrapped = true;
c5.SetStyle(style);
// Save Excel File
wb.Save(outputDir + "outputUseExplicitLineBreaks.xlsx");
Console.WriteLine("UseExplicitLineBreaks executed successfully.");
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create Workbook Object
Workbook wb = new Workbook();
// Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
// Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;
// Increase the width of First Column Width
cell.SetColumnWidth(0, 35);
// Increase the height of first row
cell.SetRowHeight(0, 36);
// Add Text to the Firts Cell
cell[0, 0].PutValue("I am using the latest version of Aspose.Cells to test this functionality");
// Make Cell's Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);
// Save Excel File
wb.Save(outputDir + "outputWrapText.xlsx");
Console.WriteLine("WrapText executed successfully.");
}
public void init_data(string v_pmkey = "id", int v_row_begin = 3)
{
Excel.Cells data = m_sheet.Cells;
_pmKey = v_pmkey;
_data = new Dictionary <string, List <object> >();
int col = m_header.get_col(v_pmkey);
if (col < 0)
{
Debug.Error(string.Format("{0}中没找到名为{1}的列", m_sheet_name, v_pmkey));
return;
}
pm_index = new Dictionary <string, int>();
string[] headerNames = m_header.Data;
for (int i = v_row_begin; i < 100000; i++)
{
object test_obj = data[i, col].Value;
if (test_obj == null)
{
break;
}
if (!pm_index.ContainsKey(test_obj.ToString()))
{
pm_index.Add(test_obj.ToString(), i);
List <object> rowData = new List <object>();
_data[test_obj.ToString()] = rowData;
for (int j = 0; j < headerNames.Length; j++)
{
rowData.Add(get_val(i, m_header[headerNames[j]]));
}
}
}
}
public _SheetHeader(EXCEL.Cells v_datas, string[] v_shildKeys, int v_offX = 0, int v_offY = 0)
{
m_headerCnt = 0;
m_shildKeys = new HashSet <string>();
foreach (var shieldKey in v_shildKeys)
{
m_shildKeys.Add(shieldKey);
}
for (int column = v_offX; column < 200; column++)
{
string englishName = v_datas[v_offY, column].StringValue;
if (string.IsNullOrEmpty(englishName))
{
break;
}
m_headerCnt++;
ExcelHeaderDecorate data = new ExcelHeaderDecorate();
data.init(v_datas[v_offY + 0, column].StringValue.Trim(),
v_datas[v_offY + 1, column].StringValue,
v_datas[v_offY + 2, column].StringValue,
column);
data._colIndex = column;
add_header(data);
}
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create Workbook Object
Workbook wb = new Workbook();
//Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
//Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;
//Increase the width of First Column Width
cell.SetColumnWidth(0, 35);
//Increase the height of first row
cell.SetRowHeight(0, 65);
//Add Text to the Firts Cell with Explicit Line Breaks
cell[0, 0].PutValue("I am using\nthe latest version of \nAspose.Cells to \ntest this functionality");
//Make Cell's Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);
// Save Excel File
wb.Save(dataDir + "WrappingText.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create Workbook Object
Workbook wb = new Workbook();
// Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
// Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;
// Increase the width of First Column Width
cell.SetColumnWidth(0, 35);
// Increase the height of first row
cell.SetRowHeight(0, 65);
// Add Text to the Firts Cell with Explicit Line Breaks
cell[0, 0].PutValue("I am using\nthe latest version of \nAspose.Cells to \ntest this functionality");
// Make Cell's Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);
// Save Excel File
wb.Save(dataDir + "WrappingText.out.xlsx");
// ExEnd:1
}
/// <summary>
/// 把DataTabel转换成Excel文件
/// </summary>
/// <param name="datatable">DataTable对象</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//int i = cells.GetColumnWidth(1);
cells.SetColumnWidth(0, 5);
cells.SetColumnWidth(1, 16);
cells.SetColumnWidth(2, 10);
cells.SetColumnWidth(3, 15);
cells.SetColumnWidth(4, 10);
cells.SetColumnWidth(5, 10);
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return(true);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
/// <summary>
/// 将Excel转成DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static System.Data.DataTable ReadExcel(string fileName)
{
Workbook book = new Workbook(fileName);
Worksheet sheet = book.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
return(cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true));
}
public object getData(Excel.Cells v_data, int v_row, string v_title)
{
int col = this[v_title];
if (col == -1)
{
return(null);
}
return(v_data[v_row, col].Value);
}
protected void Button1_Click(object sender, EventArgs e)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(Server.MapPath("./") + "Template\\mb.xls");
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells[4, 1].PutValue("50H工具箱");
XlsSaveOptions saveOptions = new XlsSaveOptions();
workbook.Save(Response, "result.xls", ContentDisposition.Inline, saveOptions);
}
public bool setData(Excel.Cells v_data, int v_row, string v_title, object v_val)
{
int col = this[v_title];
if (col == -1)
{
return(false);
}
v_data[v_row, col].Value = v_val;
return(true);
}
/// <summary>
/// 把DataTabel转换成Excel文件
/// </summary>
/// <param name="datatable">DataTable对象</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (Exception ex)
{
error = error + " DataTableToExcel: " + ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
}
}
workbook.Save(filepath);
return(true);
}
catch (Exception ex)
{
error = error + " DataTableToExcel: " + ex.Message;
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools));
return(false);
}
}
public static DataTable ReadData(string strFileName, ref string message)
{
DataTable dt = new DataTable();
try
{
// CellDLL.Workbook workbook = new CellDLL.Workbook(strFileName, new CellDLL.LoadOptions(CellDLL.LoadFormat.Excel97To2003)); //(strFileName, FileFormatType.Default);
CellDLL.Workbook workbook = new CellDLL.Workbook(strFileName);
CellDLL.Cells cellList = null;
CellDLL.Worksheet sheet = workbook.Worksheets[0];
cellList = sheet.Cells;
int startIndex = cellList.MinColumn;
int lastIndex = cellList.MaxColumn;
//获取列头
for (int i = startIndex; i <= lastIndex; i++)
{
if (cellList[0, i] == null)
{
continue;
}
dt.Columns.Add(cellList[0, i].StringValue);
}
//获取数据
DataRow dataRow = null;
for (int i = cellList.MinDataRow + 1; i <= cellList.MaxDataRow; i++)
{
dataRow = dt.NewRow();
for (int j = startIndex; j <= lastIndex; j++)
{
if (cellList[i, j] == null)
{
continue;
}
dataRow[j] = cellList[i, j].StringValue;
}
dt.Rows.Add(dataRow);
}
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// 将excel数据存到DataTable
/// </summary>
public DataTable ExcelToDataTable(string file)
{
DataTable dt = new System.Data.DataTable();
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook();
workBook.Open(file, FileFormatType.Excel2007Xlsx);
Aspose.Cells.Worksheet sheet = workBook.Worksheets[1];
Aspose.Cells.Cells cells = sheet.Cells;
dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
return(dt);
}
public void init(Excel.Worksheet v_sheet, int v_header_row = 0)
{
Excel.Cells data = v_sheet.Cells;
for (int i = 0; i < 100; i++)
{
string header = Convert.ToString(data[v_header_row, i].Value);
if (string.IsNullOrEmpty(header))
{
break;
}
m_header.Add(header, i);
}
}
public void init(Excel.Cells v_data, int v_row, SheetHeader v_header)
{
sheetName = v_header.getData(v_data, v_row, "sheet名") as string;
//optFileName = v_header.getData(v_data, v_row, "导出文件") as string;
dataOffX = Convert.ToInt32(v_header.getData(v_data, v_row, "数据偏移X"));
dataOffY = Convert.ToInt32(v_header.getData(v_data, v_row, "数据偏移Y"));
optCliFileName = v_header.getData(v_data, v_row, "导出客户端文件") as string;
optCliLanguage = getLuaguage(optCliFileName);
optSrvFileName = v_header.getData(v_data, v_row, "导出服务端文件") as string;
optSrvLanguage = getLuaguage(optSrvFileName);
pmKey = _getPmKey(v_header.getData(v_data, v_row, "主键") as string);
string shieldColNames = v_header.getData(v_data, v_row, "屏蔽字段") as string;
if (string.IsNullOrEmpty(shieldColNames))
{
shildKeys = new string[0];
}
else
{
shildKeys = (v_header.getData(v_data, v_row, "屏蔽字段") as string).Split(',', '，');
}
Object optCols = v_header.getData(v_data, v_row, "是否导出");
if (optCols == null)
{
optCols = false;
}
if (optCols is bool)
{
isOpt = (bool)optCols;
}
else
{
isOpt = optCols.ToString().Equals("TRUE");
}
note = v_header.getData(v_data, v_row, "表注释") as string;
Object dataPersistence = v_header.getData(v_data, v_row, "是否导出");
if (dataPersistence == null)
{
dataPersistence = false;
}
if (dataPersistence is bool)
{
isDataPersistence = (bool)dataPersistence;
}
else
{
isDataPersistence = optCols.ToString().Equals("TRUE");
}
}
public void readHeader(Excel.Worksheet v_sheet, int v_headerRow = 0)
{
_workSheet = v_sheet;
Excel.Cells data = _workSheet.Cells;
for (int i = 0; i < 100; i++)
{
if (data[v_headerRow, i].Value == null)
{
break;
}
_headerIndex.Add(data[v_headerRow, i].StringValue, _header.Count);
_header.Add(data[v_headerRow, i].StringValue);
}
}
public bool init(EXCEL.Worksheet v_workSheet, IndexSheetData v_indexData, int v_rowBegin = 3)
{
indexData = v_indexData;
sheetName = v_indexData.sheetName;
EXCEL.Cells datas = v_workSheet.Cells;
header = new _SheetHeader(datas, v_indexData.shildKeys, v_indexData.dataOffX, v_indexData.dataOffY);
data = new List <CellValue[]>();
for (int row = v_indexData.dataOffY + v_rowBegin; row < 100000; row++)
{
if (datas[row, 0].Value == null)
{
break;
}
CellValue[] row_data = new CellValue[header.HeaderLen];
for (int colidx = 0; colidx < header.HeaderLen; colidx++)
{
int col = colidx + v_indexData.dataOffX;
try
{
row_data[colidx] = header.checkData(col, datas[row, colidx]);
}
catch (Exception ex)
{
Debug.Error("在导出表\"{0}\" sheet \"{1}\" 单元格{2}{3}时发生了错误，单元格内容为{4}，错误信息如下:\r\n{5}",
v_workSheet.Workbook.FileName, v_indexData.sheetName, Tools.getColName(col), row + 1, datas[row, colidx].Value, ex.ToString());
return(false);
}
}
//添加注释数据
if (header.Note != null)
{
CellValue noteCell = row_data[header.Note.Col];
if (noteCell is MissVal)
{
notes.Add(null);
}
else
{
notes.Add(noteCell.ToString());
}
}
else
{
notes.Add(null);
}
data.Add(row_data);
}
return(true);
}
public void init()
{
m_datas = new Dictionary <int, eloConfigData>();
Excel.Workbook book = new Aspose.Cells.Workbook(Config.elo_data_path.path);
Excel.Worksheet sheet = book.Worksheets[Config.elo_data_path.sheets[0]];
Excel.Cells data = sheet.Cells;
for (int row = 3; row <= 1000; row++)
{
if (data[row, 0].Value == null || string.IsNullOrWhiteSpace(data[row, 0].Value.ToString()))
{
break;
}
m_datas[data[row, 0].IntValue] = new eloConfigData(data[row, 1].IntValue, data[row, 2].IntValue);
}
}
public static bool ListsToExcelFile(string filepath, IList[] lists, out string error)
{
error = "";
//----------Aspose变量初始化----------------
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------输入数据-------------
int nRow = 0;
sheet.Pictures.Clear();
cells.Clear();
foreach (IList list in lists)
{
for (int i = 0; i <= list.Count - 1; i++)
{
try
{
System.Console.WriteLine(i.ToString() + " " + list[i].GetType());
if (list[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//插入图片数据
System.Drawing.Image image = (System.Drawing.Image)list[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(list[i]);
}
}
catch (System.Exception e)
{
error = error + e.Message;
}
}
nRow++;
}
//-------------保存-------------
workbook.Save(filepath);
return(true);
}
public void calMonsterData(Excel.Worksheet v_monsterSheet)
{
Excel.Cells data = v_monsterSheet.Cells;
SheetHeader header = new SheetHeader();
header.readHeader(v_monsterSheet, 1);
m_monsterAttr = new List <MonsterAttr>();
//读取设置数据
for (int i = 2; true; i++)
{
object oid = header.getData(data, i, "ID");
if (oid == null || string.IsNullOrWhiteSpace(oid.ToString()))
{
break;
}
MonsterAttr tmp = new MonsterAttr();
tmp.id = Convert.ToInt32(oid);
tmp.note = header.getData(data, i, "备注").ToString();
tmp.groupID = Convert.ToInt32(header.getData(data, i, "组ID"));
tmp.level = Convert.ToInt32(header.getData(data, i, "等级"));
tmp.iniAngry = Convert.ToInt32(header.getData(data, i, "初始怒气"));
tmp.angryRecover = Convert.ToInt32(header.getData(data, i, "怒气回复"));
tmp.killMonsterRound = Convert.ToSingle(header.getData(data, i, "玩家杀怪回合数"));
tmp.hpCost = Convert.ToSingle(header.getData(data, i, "消耗血量"));
tmp.phisicalDefPercent = Convert.ToSingle(header.getData(data, i, "物防百分比"));
tmp.magicalDefPercent = Convert.ToSingle(header.getData(data, i, "法防百分比"));
tmp.playerCureNum = Convert.ToSingle(header.getData(data, i, "玩家治疗数"));
tmp.monsterCureNum = Convert.ToSingle(header.getData(data, i, "怪物治疗数"));
m_monsterAttr.Add(tmp);
}
//进行计算,并填表
for (int i = 0; i < m_monsterAttr.Count; i++)
{
MonsterAttr cur = m_monsterAttr[i];
int playerIdx = getPlayerAttrIdx(cur.groupID, cur.level);
BaseAttrData playerAttr = m_playerAttrdatas[playerIdx];
cur.playerAttr = playerAttr;
cur.calMonsterAttr();
header.setData(data, i + 2, "玩家血量", playerAttr.Hp);
header.setData(data, i + 2, "玩家攻击", playerAttr.Atk);
header.setData(data, i + 2, "玩家物防", playerAttr.PhisicalDef);
header.setData(data, i + 2, "玩家法防", playerAttr.MagicalDef);
header.setData(data, i + 2, "怪物血量", cur.monsterAttr.Hp);
header.setData(data, i + 2, "怪物攻击", cur.monsterAttr.Atk);
header.setData(data, i + 2, "怪物物防", cur.monsterAttr.PhisicalDef);
header.setData(data, i + 2, "怪物法防", cur.monsterAttr.MagicalDef);
}
}
public static void convert(SheetHeader v_et1, SheetHeader v_et2, string[] v_concernCols1, string[] v_concernCols2)
{
Excel.Cells data1 = v_et1.data;
Excel.Cells data2 = v_et2.data;
int pmIdx1 = v_et1.PmIndex;
int concernColLen = v_concernCols1.Length;
int[] sheet1_concernCols = new int[concernColLen];
int[] sheet2_concernCols = new int[concernColLen];
for (int i = 0; i < concernColLen; i++)
{
sheet1_concernCols[i] = v_et1[v_concernCols1[i]];
if (sheet1_concernCols[i] == -1)
{
Debug.Exception("表1没有设置关心列{0}", v_concernCols1[i]);
return;
}
sheet2_concernCols[i] = v_et2[v_concernCols2[i]];
if (sheet2_concernCols[i] == -1)
{
Debug.Exception("表2没有设置关心列{0}", v_concernCols2[i]);
return;
}
}
for (int i = 0; i < v_et1.DataRows; i++)
{
string pmVal = data1[i + v_et1.RowBegin, pmIdx1].StringValue;
int sheet2Row = v_et2.getPMRow(pmVal);
if (sheet2Row == -1)
{
Debug.Exception("表2中没找到主键{0}", pmVal);
return;
}
for (int j = 0; j < concernColLen; j++)
{
var cellVal = data1[i + v_et1.RowBegin, sheet1_concernCols[j]].Value;
if (cellVal != null && cellVal.ToString() != "#N/A")
{
data2[sheet2Row, sheet2_concernCols[j]].Value = cellVal;
}
}
}
}
/// <summary>
/// DataTableToExcel
/// DataTabel转换成Excel文件
///
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径<</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
{
error = "";
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(fromfile);
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------插入数据-------------
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
cells.InsertRow(beginRow);
for (int i = 0; i < colNameList.Count; i++)
{
string colName = colNameList[i].ToString();
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (colName == datatable.Columns[j].ColumnName)
{
object temp = row[datatable.Columns[j].ColumnName];
cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
break;
}
}
}
}
catch (System.Exception e)
{
error = error + " DataTableInsertToExcel: " + e.Message;
}
}
//-------------保存-------------
workbook.Save(fromfile);
return(true);
}
public void readData(Excel.Worksheet v_sheet)
{
Excel.Cells data = v_sheet.Cells;
SheetHeader header = new SheetHeader();
header.readHeader(v_sheet, 1);
for (int i = 2; i < 100000; i++)
{
object idIdx = header.getData(data, i, "等级");
if (idIdx == null || string.IsNullOrWhiteSpace(idIdx.ToString()))
{
break;
}
int groupId = Convert.ToInt32(header.getData(data, i, "组ID"));
List <int> arrIndex = null;
if (!m_playerDataIndex.ContainsKey(groupId))
{
arrIndex = new List <int>();
m_playerDataIndex.Add(groupId, arrIndex);
}
else
{
arrIndex = m_playerDataIndex[groupId];
}
arrIndex.Add(m_playerDatas.Count);
if (arrIndex == null)
{
arrIndex = new List <int>();
m_playerDataIndex.Add(groupId, arrIndex);
}
arrIndex.Add(m_playerDatas.Count);
playerData pd = new playerData();
string[] strIpt = new string[titles.Length];
for (int j = 0; j < titles.Length; j++)
{
object tmp = header.getData(data, i, titles[j]);
if (tmp == null)
{
Debug.Info("读取第{0}行第{1}列时发生错误", i, titles[j]);
}
strIpt[j] = Convert.ToString(tmp);
}
pd.init(strIpt);
m_playerDatas.Add(pd);
}
}
