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