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 (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="filepath">Excel文件的全路径</param> /// <param name="datatable">DataTable:返回值</param> /// <param name="error">错误信息:返回错误信息,没有错误返回""</param> /// <returns>true:函数正确执行 false:函数执行错误</returns> public bool ExcelFileToDataSet(string filepath, string guidStr, out DataSet dataset, out string error) { error = ""; //dataset = null; try { if (File.Exists(filepath) == false) { error = "文件不存在"; dataset = null; return(false); } else { dataset = new DataSet(); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Open(filepath); int tableCount = workbook.Worksheets.Count; for (int i = 0; i < tableCount; i++) { DataTable datatable = new DataTable(); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i]; if (worksheet.Cells.Rows.Count > 1) { datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1); datatable.TableName = workbook.Worksheets[i].Name; if (datatable.Rows.Count > 2) { for (int k = 0; k < datatable.Columns.Count; k++) { datatable.Columns[k].ColumnName = datatable.Rows[1][k].ToString(); } datatable.Rows[1].Delete(); datatable.Rows[0].Delete(); datatable.AcceptChanges(); } dataset.Tables.Add(datatable.Copy()); dataset.Tables[dataset.Tables.Count - 1].TableName = workbook.Worksheets[i].Name; } } return(true); } catch (System.Exception e) { logger.Error(string.Format("{0}:[{1}]:[{2}]:[{3}]", "ExcelFileToDataSet", filepath, "数据保存异常", e.Message + e.StackTrace)); DBLogger.Insert(DBLogger.GetLoggerInfo(filepath, e.Message + e.StackTrace, guidStr, 0)); error = e.Message; throw e; return(false); } }
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); } }
/// <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); }
/// <summary> /// 读取Excel文件到DataTable;全部单元格的值读取采用 StringValue /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="sheetIndex">默认读取第 1 个工作表</param> /// <param name="exportColumnName">将首行的值设置为DataColumn, 默认设置</param> /// <param name="skipErrorValue">忽略转换出现异常的值</param> /// <returns></returns> public static DataTable Excel2DataTableAsString(string filePath, int sheetIndex = 0, bool exportColumnName = true) { try { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[sheetIndex]; return(worksheet2DataTableAsString(worksheet: worksheet, exportColumnName: exportColumnName)); } catch (Exception ex) { throw new Exception("读取Excel失败", ex); } }
public static DataTable ExcelToDataTable2(string sheetName, string pathName = "") { if (pathName == "") { pathName = Application.StartupPath + "\\Config\\ACCESS.xlsx"; } sheetName = sheetName.Substring(0, sheetName.Length - 1);//主要用于剔除在对excelSQL加的$ Aspose.Cells.Workbook workbook1 = new Aspose.Cells.Workbook(); workbook1.Open(pathName); Aspose.Cells.Worksheet cellSheet1 = workbook1.Worksheets[sheetName]; Cells cells1 = cellSheet1.Cells; return(cells1.ExportDataTableAsString(0, 0, cells1.MaxDataRow + 1, cells1.MaxDataColumn + 1, 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 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 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); }
/// <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); } }
public static void Main() { //ExStart:1 // The path to the documents directory. string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //Define memory stream object System.IO.MemoryStream objImage; //Define web client object System.Net.WebClient objwebClient; //Define a string which will hold the web image url string sURL = "http://www.aspose.com/Images/aspose-logo.jpg"; try { //Instantiate the web client object objwebClient = new System.Net.WebClient(); //Now, extract data into memory stream downloading the image data into the array of bytes objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL)); //Create a new workbook Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); //Get the first worksheet in the book Aspose.Cells.Worksheet sheet = wb.Worksheets[0]; //Get the first worksheet pictures collection Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures; //Insert the picture from the stream to B2 cell pictures.Add(1, 1, objImage); //Save the excel file wb.Save(dataDir + "webimagebook.out.xlsx"); } catch (Exception ex) { //Write the error message on the console Console.WriteLine(ex.Message); } //ExEnd:1 }
static void Main(string[] args) { //Instantiate a new Workbook. Workbook workbook = new Workbook(); //Specify the template Excel file path. string myPath = "Book1.xls"; //Open the Excel file. workbook.Open(myPath); //Get the first sheet. Aspose.Cells.Worksheet objSheet = workbook.Worksheets["Sheet1"]; findNow(objSheet, "test"); workbook.Save(myPath); }
// private void CrateExcel(DataTable forExcelDt, string SheetName, string FullPath, string xlsName) { forExcelDt.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(FullPath); designer.Open(xlsMdlPath); designer.SetDataSource(forExcelDt); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(SheetName); string newXls = xlsName + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); ExcelHelper.deletefile(xlspath); designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); this.PageState.Add("fileName", "../Excel/tempexcel/" + newXls); }
private void ImpExcel() { string where = string.Empty; //权限过滤 var Ent = SurveyQuestion.TryFind(SurveyId); if (Ent != null && Ent.IsFixed == "2") { CommPowerSplit PS = new CommPowerSplit(); if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName)) { } else { UserContextInfo UC = new UserContextInfo(); where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' "; } } tmpSQL = tmpSQL.Replace("##QUERY##", where); tmpSQL = tmpSQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB); string sql = string.Format(tmpSQL, SurveyId); string path = RequestData.Get <string>("path"); string fileName = RequestData.Get <string>("fileName"); string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss"); DataTable forExcelDt = DataHelper.QueryDataTable(sql); if (forExcelDt.Rows.Count > 0) { forExcelDt.TableName = "data"; WorkbookDesigner designer = new WorkbookDesigner(); string xlsMdlPath = Server.MapPath(path); designer.Open(xlsMdlPath); designer.SetDataSource(forExcelDt); designer.Process(); Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName); string newXls = xlsName + ".xls"; System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel")); ExcelHelper.deletefile(xlspath); designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003); this.PageState.Add("fileName", "/Excel/tempexcel/" + newXls); } }
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(); if (File.Exists(filepath)) { workbook.Open(filepath); } Aspose.Cells.Worksheet sheet = workbook.Worksheets.Add(datatable.TableName); 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++) { cells[nRow, i].PutValue(row[i]); } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } workbook.Save(filepath, FileFormatType.Default); return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, out string error) { error = ""; datatables = null; int nSheetsCount = 0; try { if (File.Exists(filepath) == false) { error = "文件不存在"; datatables = null; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Open(filepath); nSheetsCount = workbook.Worksheets.Count; datatables = new DataTable[nSheetsCount]; for (int i = 0; i < nSheetsCount; i++) { Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i]; datatables[i] = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1); //-------------图片处理------------- Aspose.Cells.Pictures pictures = worksheet.Pictures; if (pictures.Count > 0) { string error2 = ""; if (InsertPicturesIntoDataTable(pictures, datatables[i], out datatables[i], out error2) == false) { error = error + error2; } } } return(true); } catch (System.Exception e) { error = e.Message; return(false); } }
/// <summary> /// 生成Excel文件流 /// </summary> /// <param name="result"></param> /// <param name="isFieldNameShown"></param> /// <param name="firstRow"></param> /// <param name="firstColumn"></param> /// <param name="saveFormat"></param> /// <param name="rowNumber"></param> /// <param name="colNumber"></param> /// <param name="insertRows"></param> /// <param name="dateFormatString"></param> /// <param name="convertStringToNumber"></param> /// <param name="sheetName"></param> /// <returns></returns> public static byte[] GenerateExcelFileStream(DataTable result, bool isFieldNameShown , int firstRow, int firstColumn, SaveFormat saveFormat, int rowNumber, int colNumber, bool insertRows, string dateFormatString , bool convertStringToNumber, string sheetName = "Sheet1") { AsposeXls.Workbook workbook = new AsposeXls.Workbook(); AsposeXls.Worksheet sheet = workbook.Worksheets[0]; sheet.Name = sheetName; sheet.FreezePanes(1, 1, 1, 0); // 冻结第一行 //sheet.Cells.ImportDataTable(result, isFieldNameShown, firstRow, firstColumn // , rowNumber, colNumber, insertRows, dateFormatString, true); sheet.Cells.ImportDataTable(result, true, 0, 0); sheet.AutoFitColumns(); // 让各列自适应宽度,这个很有用 using (MemoryStream stream = new MemoryStream()) { workbook.Save(stream, saveFormat); return(stream.ToArray()); } }
public static bool Dtc_DataTableToExcel(DataTable datatable, string filepath, out string error) { string file = Application.StartupPath + "\\13dtc" + DateTime.Now.ToString() + "id.xls"; datatable.Columns.Remove("ID"); 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(0, 25); cells.SetColumnWidth(1, 17); cells.SetColumnWidth(2, 28.5); cells.SetColumnWidth(3, 10); cells.SetColumnWidth(4, 10); cells.SetColumnWidth(5, 25); 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); }
//aspose private void button3_Click(object sender, EventArgs e) { Workbook workbook = new Workbook(@"2year.xlsx"); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; Cell cell = worksheet.Cells[2, 0]; cell.PutValue(40000); var options = new CalculationOptions(); options.IgnoreError = true; worksheet.CalculateFormula(options, true); workbook.Save(@"test2.xlsx", Aspose.Cells.SaveFormat.Xlsx); Workbook workbook2 = new Workbook(@"2year.xlsx"); Aspose.Cells.Worksheet worksheet2 = workbook2.Worksheets[0]; Cell cell2 = worksheet2.Cells[2, 2]; MessageBox.Show(cell2.Value.ToString()); }
public static void Run() { //Output directory string outputDir = RunExamples.Get_OutputDirectory(); try { // Define a string which will hold the web image url string sURL = "http://www.aspose.com/Images/aspose-logo.jpg"; // Instantiate the web client object System.Net.WebClient objwebClient = new System.Net.WebClient(); // Now, extract data into memory stream downloading the image data into the array of bytes MemoryStream objImage = new System.IO.MemoryStream(objwebClient.DownloadData(sURL)); // Create a new workbook Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); // Get the first worksheet in the book Aspose.Cells.Worksheet sheet = wb.Worksheets[0]; // Get the first worksheet pictures collection Aspose.Cells.Drawing.PictureCollection pictures = sheet.Pictures; // Insert the picture from the stream to B2 cell pictures.Add(1, 1, objImage); // Save the excel file wb.Save(outputDir + "outputLoadWebImage.xlsx"); } catch (Exception ex) { // Write the error message on the console Console.WriteLine(ex.Message); } Console.WriteLine("LoadWebImage executed successfully."); }
/// <summary> /// /// </summary> /// <param name="xlsPath"></param> public DataTable ImportUserInfo(string xlsPath = "") { try { xlsPath = string.IsNullOrWhiteSpace(xlsPath) ? Environment.CurrentDirectory + @"\test.xlsx" : xlsPath; Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(xlsPath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; Cells cells = worksheet.Cells; DataTable datatable = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true); return(datatable); } catch (Exception ex) { throw ex; //MessageBox.Show(string.Format("您选择的Excel数据格式错误[{0}]", ex.Message)); } }
/// <summary> /// 读取Excel文件到DataSet;全部单元格的值读取采用 StringValue /// </summary> /// <param name="filePath">Excel文件路径</param> /// <param name="exportColumnName">将首行的值设置为DataColumn, 默认设置</param> /// <returns></returns> public static DataSet Excel2DataSetAsString(string filePath, bool exportColumnName = true) { Aspose.Cells.Workbook workbook = null; try { workbook = new Workbook(filePath); DataSet ds = new DataSet(); for (int sheetCount = 0; sheetCount < workbook.Worksheets.Count; sheetCount++) { Aspose.Cells.Worksheet worksheet = workbook.Worksheets[sheetCount]; var dt = worksheet2DataTableAsString(worksheet: worksheet, exportColumnName: exportColumnName); ds.Tables.Add(dt); } return(ds); } catch (Exception ex) { throw new Exception("读取Excel失败", ex); } }
public void readHeader(Excel.Worksheet v_sheet, int v_headerRow = 0) { _workSheet = v_sheet; Excel.Cells data = _workSheet.Cells; int i = 0; try { for (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); } } catch (Exception ex) { Debug.Error("添加重复建{0},系统报错是:\r\n{1}", data[v_headerRow, i].StringValue, ex.ToString()); } }
/// <summary> /// Excel文件转换为DataTable.(指定开始行列,以及导入的记录行数) /// </summary> /// <param name="filepath">Excel文件的全路径</param> /// <param name="datatable">DataTable:返回值</param> /// <param name="iFirstRow">起始行</param> /// <param name="iFirstCol">起始列</param> /// <param name="rowNum">导入行数</param> /// <param name="colNum">列数</param> /// <param name="error">错误信息:返回错误信息,没有错误返回""</param> /// <returns></returns> public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, bool exportColumnName, out string error) { error = ""; datatable = null; try { if (File.Exists(filepath) == false) { error = "文件不存在"; datatable = null; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; datatable = worksheet.Cells.ExportDataTableAsString(iFirstRow, iFirstCol, rowNum + 1, colNum + 1, exportColumnName); return(true); } catch (System.Exception e) { error = e.Message; return(false); } }
static void Main(string[] args) { //Instantiate a new Workbook. Workbook workbook = new Workbook(); //Specify the template Excel file path. string myPath = "Book1.xls"; //Open the Excel file. workbook.Open(myPath); //Get the first sheet. Aspose.Cells.Worksheet objSheet = workbook.Worksheets["Sheet1"]; //Hide the worksheet. objSheet.IsVisible = false; //Unhide the worksheet. objSheet.IsVisible = true; //Save As the Excel file. workbook.Save("Book1.xls"); }
/// <summary> /// Excel文件转换为DataTable. /// </summary> /// <param name="filepath">Excel文件的全路径</param> /// <param name="datatable">DataTable:返回值</param> /// <param name="error">错误信息:返回错误信息,没有错误返回""</param> /// <returns>true:函数正确执行 false:函数执行错误</returns> public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error) { error = ""; datatable = null; try { if (File.Exists(filepath) == false) { error = "文件不存在"; datatable = null; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Open(filepath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1); return(true); } catch (System.Exception e) { error = e.Message; return(false); } }
/// <summary> /// Excel文件转换为DataTable. /// </summary> /// <param name="filepath">Excel文件的全路径</param> /// <param name="datatable">DataTable:返回值</param> /// <param name="error">错误信息:返回错误信息,没有错误返回""</param> /// <returns>true:函数正确执行 false:函数执行错误</returns> public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, bool exportColumnName, out string error) { error = ""; datatable = null; try { if (File.Exists(filepath) == false) { error = "文件不存在"; datatable = null; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; datatable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName); datatable.TableName = worksheet.Name;//记录Sheet的名称 //-------------图片处理------------- Aspose.Cells.Drawing.PictureCollection pictures = worksheet.Pictures; if (pictures.Count > 0) { string error2 = ""; if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false) { error = error + error2; } } return(true); } catch (Exception ex) { error = ex.Message; LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(AsposeExcelTools)); return(false); } }