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); } }