public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
int moduleIdx = workbook.VbaProject.Modules.Add(sheet);
Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[moduleIdx];
module.Codes =
"Sub ShowMessage()" + "\r\n" +
" MsgBox \"Welcome to Aspose!\"" + "\r\n" +
"End Sub";
Aspose.Cells.Drawing.Button button = sheet.Shapes.AddButton(2, 0, 2, 0, 28, 80);
button.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
button.Font.Name = "Tahoma";
button.Font.IsBold = true;
button.Font.Color = System.Drawing.Color.Blue;
button.Text = "Aspose";
button.MacroName = sheet.Name + ".ShowMessage";
workbook.Save(outputDir + "outputAssignMacroToFormControl.xlsm");
Console.WriteLine("AssignMacroToFormControl executed successfully.");
}
public void MainExport(DataTable dt)
{
string filepath = "";
Aspose.Cells.Workbook xlBook = new Aspose.Cells.Workbook();
//清除默认sheet
xlBook.Worksheets.Clear();
//设置第一个sheet和name
xlBook.Worksheets.Add("日占比");
Aspose.Cells.Worksheet ws1 = xlBook.Worksheets[0];
RectangleExport(dt, ws1);
//设置第一个sheet和name
xlBook.Worksheets.Add("客户总量");
Aspose.Cells.Worksheet ws2 = xlBook.Worksheets[1];
RectangleExport(dt, ws2);
//保存地址
if (dt.Rows.Count > 60000)
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + "牙模盒使用记录" + ".xlsx";
}
else
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + "牙模盒使用记录" + ".xls";
}
//保存
xlBook.Save(filepath);
//打开
System.Diagnostics.Process.Start(filepath);
}
/// <summary>
/// 設定表頁的列寬自適應
/// </summary>
/// <param name="sheet"></param>
private void SetColumnAuto(ref Aspose.Cells.Worksheet sheet)
{
Aspose.Cells.Cells cells = sheet.Cells;
//获取页面最大列数
int columnCount = cells.MaxColumn + 1;
//获取页面最大行数
int rowCount = cells.MaxRow;
for (int col = 0; col < columnCount; col++)
{
sheet.AutoFitColumn(col, 0, rowCount);
}
for (int col = 0; col < columnCount; col++)
{
int pixel = cells.GetColumnWidthPixel(col) + 10;
if (pixel > 255)
{
cells.SetColumnWidthPixel(col, 255);
}
else
{
cells.SetColumnWidthPixel(col, pixel);
}
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">Ilist集合</param>
/// <param name="filepath">保存的地址</param>
public static bool Export <T>(IList <T> data, string filepath)
{
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = (Aspose.Cells.Worksheet)workbook.Worksheets[0];
PropertyInfo[] ps = typeof(T).GetProperties();
var colIndex = "A";
foreach (var p in ps)
{
sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称 要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称
int i = 2;
foreach (var d in data)
{
sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
i++;
}
colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头 A1/A2/
}
workbook.Save(filepath);
GC.Collect();
return(true);
}
catch (Exception)
{
return(false);
}
}
private void ExportXLS(string docno, string caseID, string Bu, string Building)
{
string templatePath = string.Empty;
Aspose.Cells.License lic = new Aspose.Cells.License();
string AsposeLicPath = System.Configuration.ConfigurationSettings.AppSettings["AsposeLicPath"].ToString();
lic.SetLicense(AsposeLicPath);
templatePath = Page.Server.MapPath("~/Web/E-Report/ReportTemp.xlsx");
//Instantiate a new Workbook object.
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(templatePath);
Aspose.Cells.Worksheet sheet1 = book.Worksheets[0]; //DFX
Aspose.Cells.Worksheet sheet2 = book.Worksheets[1]; // PMFEA
Aspose.Cells.Worksheet sheet3 = book.Worksheets[2]; // IssuesList
Aspose.Cells.Worksheet sheet4 = book.Worksheets[3]; // CTQ
// REPLACE PUBLIC FIELDS
BindExcel(ref sheet1, caseID, Bu, Building, docno);
BindPFMA(ref sheet2, caseID, Bu, Building, docno);
BindIssuesList(ref sheet3, caseID, Bu, Building, docno);
BindCTQ(ref sheet4, caseID, Bu, Building, docno);
SetColumnAuto(ref sheet1);
this.Response.Clear();
book.Save("NPI_Report.xls",
Aspose.Cells.FileFormatType.Excel97To2003,
Aspose.Cells.SaveType.OpenInExcel,
this.Response,
System.Text.Encoding.UTF8);
}
private void SetSignContentToSheet(Aspose.Cells.Worksheet sheet, string fieldName, string fieldValue)
{
IUserService service = FormulaHelper.GetService <IUserService>();
var cell = sheet.Cells.Find(fieldName, null, new Aspose.Cells.FindOptions()
{
LookInType = Aspose.Cells.LookInType.Values, LookAtType = Aspose.Cells.LookAtType.StartWith
});
if (cell != null)
{
var cellValue = cell.Value.ToString();
//清除内容
sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);
//带_sign,则显示签字图片
if (cellValue.ToLower().Contains("_sign"))
{
byte[] signImg = service.GetSignImg(fieldValue);
if (signImg != null)
{
MemoryStream ms = new MemoryStream(signImg);
sheet.Pictures.Add(cell.Row, cell.Column, cell.Row + 1, cell.Column + 1, ms);
}
}
//显示名称
else
{
cell.PutValue(fieldValue);
}
}
}
private void ExportXLS(string caseID, string Bu, string FormNo)
{
string templatePath = string.Empty;
Aspose.Cells.License lic = new Aspose.Cells.License();
string AsposeLicPath = System.Configuration.ConfigurationSettings.AppSettings["AsposeLicPath"].ToString();
lic.SetLicense(AsposeLicPath);
templatePath = Page.Server.MapPath("~/Web/E-Report/TempPrelaunch .xlsx");
//Instantiate a new Workbook object.
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(templatePath);
Aspose.Cells.Worksheet sheet1 = book.Worksheets[0];
Aspose.Cells.Worksheet sheet2 = book.Worksheets[1];
// REPLACE PUBLIC FIELDS
BindHomePage(ref sheet1, caseID, Bu);
BindCheckItem(ref sheet2, FormNo, Bu);
this.Response.Clear();
book.Save("Prelaunch_Report.xls",
Aspose.Cells.FileFormatType.Excel97To2003,
Aspose.Cells.SaveType.OpenInExcel,
this.Response,
System.Text.Encoding.UTF8);
}
/// <summary>
/// Excel 生成 HTML 文件
/// </summary>
/// <param name="excelPath">Excel 路径</param>
/// <param name="htmlPath">Html 路径</param>
/// <param name="sheetName">表单名称</param>
public static void ExcelToHtmlFile(string excelPath, string htmlPath, string sheetName = "")
{
Aspose.Cells.HtmlSaveOptions htmlSaveOptions = new Aspose.Cells.HtmlSaveOptions(Aspose.Cells.SaveFormat.Html);
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook(excelPath);
if (string.IsNullOrEmpty(sheetName))
{
workBook.Save(htmlPath, htmlSaveOptions);
}
else
{
Aspose.Cells.Workbook newWorkBook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet newWorkSheet = newWorkBook.Worksheets[0];
newWorkSheet.Copy(workBook.Worksheets[sheetName]);
newWorkBook.Save(htmlPath, htmlSaveOptions);
}
string directoryPath = string.Format("{0}/{1}_files", Path.GetDirectoryName(htmlPath), System.IO.Path.GetFileNameWithoutExtension(htmlPath));
string[] filePathList = Directory.GetFiles(directoryPath, "*.htm");
foreach (string filePath in filePathList)
{
TransformHTMLEncoding(filePath, string.Format("<script>\ndocument.write(\"<div style='color:red;font-size:10pt;font-family:Arial'>Evaluation Only. Created with Aspose.Cells for .NET.Copyright 2003 - 2018 Aspose Pty Ltd.</div>\");\n</script>"));
}
TransformHTMLEncoding(htmlPath, string.Format("<frame src=\"{0}_files/tabstrip.htm\" name=\"frTabs\" marginwidth=\"0\" marginheight=\"0\">", Path.GetFileNameWithoutExtension(htmlPath)));
}
/// <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);
}
}
public static DataTable Aspose2Data(string filePath)
{
//Aspose.Cells.License li = new Aspose.Cells.License();
//li.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(filePath);
Aspose.Cells.Worksheet ws = wk.Worksheets[0];
return(ws.Cells.ExportDataTable(1, 0, ws.Cells.Rows.Count, ws.Cells.Columns.Count));
}
/// <summary>
/// 读取excel文件导入DataTable（不依赖office com组件）
/// 作者：佳烽
/// 日期：2014-4-24
/// </summary>
/// <param name="strFileName">excel文件</param>
/// <param name="exportColumnName">是否导出列名</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(String strFileName, bool exportColumnName = true)
{
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(strFileName);
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
return(cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, exportColumnName));
}
/// <summary>
/// 設定頁面打印格式
/// </summary>
/// <param name="sheet">worksheet</param>
/// <param name="type">列印模式：直印，橫印</param>
private void SetStyle(ref Aspose.Cells.Worksheet sheet, Aspose.Cells.PageOrientationType type)
{
sheet.PageSetup.IsPercentScale = false;
sheet.PageSetup.FitToPagesWide = 1; //自動縮放為一頁寬
sheet.PageSetup.LeftMargin = 0.5;
sheet.PageSetup.RightMargin = 0.5;
sheet.PageSetup.TopMargin = 0.5;
sheet.PageSetup.BottomMargin = 0.5;
sheet.PageSetup.Orientation = type;
}
private Aspose.Cells.Cell GetDown(Aspose.Cells.Worksheet sheet, Aspose.Cells.Cell cell)
{
var row = cell.Row;
var col = cell.Column;
Aspose.Cells.Cell c = null;
while ((c = sheet.Cells[++row, col]) == null)
{
;
}
return(c);
}
/// <summary>
/// 设置武器图片路径(更新图片后调用)
/// </summary>
/// <param name="ID">武器ID</param>
/// <param name="RowIndex">武器在Excel中的行数索引</param>
/// <param name="Rarity">稀有度</param>
/// <param name="sheet">导入目标源Excel</param>
void FilePath(long ID, int RowIndex, string Rarity, Aspose.Cells.Worksheet sheet)
{
DirectoryInfo directory = new DirectoryInfo(@"E:\WeaponPanelSimulator\WeaponPanelSimulator\bin\Debug\Resources\Image\Weapon\" + Rarity + @"\");
directory.Create();
//输出稀有度目录下的所有文件与文件夹,如果目标文件夹为空 return
var files = directory.GetFileSystemInfos().ToList();
if (files.Count == 0)
{
return;
}
//如果没有找到目标的资源文件夹则return
var f = files.FirstOrDefault(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == ID.ToString());
if (f == null)
{
return;
}
var pathOfFile = f.FullName;
var fileName = files.FirstOrDefault(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == ID.ToString())?.Name;
if (string.IsNullOrEmpty(fileName))
{
return;
}
var dirt = new DirectoryInfo(pathOfFile);
var fileList = dirt.GetFiles().ToList();
if (fileList.Exists(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == "b"))
{
sheet.Cells[RowIndex, 32].PutValue(@"Resources\Image\Weapon\" + Rarity + @"\" + fileName + @"\b_" + ID + ".jpg");
}
if (fileList.Exists(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == "cjs"))
{
sheet.Cells[RowIndex, 33].PutValue(@"Resources\Image\Weapon\" + Rarity + @"\" + fileName + @"\cjs_" + ID + ".jpg");
}
if (fileList.Exists(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == "ls"))
{
sheet.Cells[RowIndex, 34].PutValue(@"Resources\Image\Weapon\" + Rarity + @"\" + fileName + @"\ls_" + ID + ".jpg");
}
if (fileList.Exists(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == "m"))
{
sheet.Cells[RowIndex, 35].PutValue(@"Resources\Image\Weapon\" + Rarity + @"\" + fileName + @"\m_" + ID + ".jpg");
}
if (fileList.Exists(x => x.Name.Remove(x.Name.IndexOf("_", StringComparison.Ordinal)) == "s"))
{
sheet.Cells[RowIndex, 36].PutValue(@"Resources\Image\Weapon\" + Rarity + @"\" + fileName + @"\s_" + ID + ".jpg");
}
}
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);
}
}
private void BindCheckItem(ref Aspose.Cells.Worksheet sheet, string FormNo, string Bu)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetPrelaunchInconformity(FormNo);
if (dtMaster.Rows.Count > 0)
{
int templateIndex = 1; //模板row起始位置
int insertIndexEnCounter = templateIndex + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
string url = "http://icm651.liteon.com/WF_PrelaunchReport/";
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
string FileName = dr["FileName"].ToString();
cells[i + templateIndex, 0].PutValue(dr["ID"].ToString());
cells[i + templateIndex, 1].PutValue(dr["Dept"].ToString());
cells[i + templateIndex, 2].PutValue(dr["CheckItem"].ToString());
cells[i + templateIndex, 3].PutValue(dr["Description"].ToString());
cells[i + templateIndex, 4].PutValue(dr["Status"].ToString());
cells[i + templateIndex, 5].PutValue(dr["Remark"].ToString());
cells[i + templateIndex, 6].PutValue(dr["Suggestion"].ToString());
cells[i + templateIndex, 7].PutValue(dr["CompleteDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompleteDate"].ToString()).ToString("yyyy/MM/dd") : dr["CompleteDate"].ToString());
cells[i + templateIndex, 8].PutValue(dr["UpateUser"].ToString());
cells[i + templateIndex, 9].PutValue(dr["UpdateTime"].ToString().Length > 0 ? Convert.ToDateTime(dr["UpdateTime"].ToString()).ToString("yyyy/MM/dd") : dr["UpdateTime"].ToString());
if (!string.IsNullOrEmpty(FileName))
{
string destFileName = url + dr["AttacheFile"].ToString();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
cells[i + templateIndex, 10].PutValue(FileName);
cells[i + templateIndex, 10].SetStyle(style);
sheet.Hyperlinks.Add(i + templateIndex, 10, 1, 1, destFileName);
cells.Merge(i + templateIndex, 10, 1, 3);
}
}
}
#endregion
}
private void BindPFMA(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetFMEAInconformity(DocNo, "", "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 6; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["Item"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["Stantion"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["Source"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["Source"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["PotentialFailureMode"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["Loess"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["Loess"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["Sev"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["Occ"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["DET"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["RPN"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["PotentialFailure"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["TargetCompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["TargetCompletionDate"].ToString()).ToString("yyyy/MM/dd") : dr["TargetCompletionDate"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["ActionsTaken"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["ResultsSev"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["ResultsOcc"].ToString());
cells[i + templateIndexDFX, 16].PutValue(dr["ResultsDet"].ToString());
cells[i + templateIndexDFX, 17].PutValue(dr["ResultsRPN"].ToString());
cells[i + templateIndexDFX, 18].PutValue(dr["WriteDept"].ToString());
}
}
#endregion
}
/// <summary>
/// 填充頁面數據
/// </summary>
/// <param name="sheet">worksheet</param>
/// <param name="docno">試產主單號</param>
/// <param name="subdocno">試產從單號</param>
private void BindExcel(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetDFXInconformity(DocNo, "", "");
//string xmlReason = string.Empty;
//string xmlPMC = string.Empty;
//string xmlResult = string.Empty;
//string xmlReasonDetail = string.Empty;
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 5; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["ItemType"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["Item"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["Location"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["Requirements"].ToString());
cells[i + templateIndexDFX, 5].PutValue("");
cells[i + templateIndexDFX, 6].PutValue(dr["Compliance"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["PriorityLevel"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["MaxPoints"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["DFXPoints"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["Comments"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["Actions"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["CompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompletionDate"].ToString()).ToString("yyyy/MM/dd"):dr["CompletionDate"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["Tracking"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["Remark"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["WriteDept"].ToString());
}
}
#endregion
}
/// <summary>
/// 将DataTable，导出为Excel
/// </summary>
/// <param name="dt">DataTable对象</param>
/// <param name="path">存储路径</param>
/// <param name="sheet">Sheet名称</param>
/// <returns></returns>
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path, string sheet)
{
bool succeed = false;
if (dt != null)
{
try
{
// Excel
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = sheet;
// 开始行、结束行；开始列、结束列
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
// 列标题
for (int i = 0; i < colCount; i++)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
s.Font.IsBold = true;
cellSheet.Cells[0, i].SetStyle(s);
cellSheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
// 单元格数据
for (int i = 1; i <= rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[i, j].PutValue(dt.Rows[i - 1][j].ToString());
}
}
cellSheet.AutoFitColumns();
path = System.IO.Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
succeed = false;
}
}
return(succeed);
}
public static int GetEndRow(Aspose.Cells.Worksheet ws, int StartRow)
{
while (true)
{
string sValue = ws.Cells[StartRow, 0].Value == null ? "" : ws.Cells[StartRow, 0].Value.ToString();
if (sValue != "")
{
StartRow++;
}
else
{
break;
}
}
return(StartRow);
}
//使用Aspose.Cells.dll, 可以不依靠Microsoft Excel也能灵活读写数据, 提供等同与Excel的功能
//避免实际实施过程中office版本问题造成过多的Bug
//且能读取csv格式
/// <summary>
/// 使用Aspose.Cells.dll, 可以不依靠Microsoft Excel也能灵活读写数据, 提供等同与Excel的功能
/// </summary>
/// <param name="fileFullPath"></param>
/// <param name="HDR">表格内是否包含列名</param>
/// <returns></returns>
public static System.Data.DataTable ExcelToDataTableByAspose(string fileFullPath, bool HDR)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fileFullPath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = worksheet.Cells;
//该方法得到的表格中，如果存在单元格内容第一个字符为0的纯数字字符串会忽略0
System.Data.DataTable dataTable = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, HDR);
//该方法得到的表格中，如果存在单元格内容为10个以上的纯数字会将其变成科学记数法
//DataTable dataTable2= cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, HDR);
workbook = null;
worksheet = null;
cells = null;
GC.Collect();
return(dataTable);
}
private void BindHomePage(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetCLCAInconformity(DocNo, "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 7; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["PROCESS"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["CTQ"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["CONTROL_TYPE"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["ACT"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["RESULT"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["DESCRIPTION"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["ROOT_CAUSE"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["D"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["M"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["P"].ToString());
cells[i + templateIndexDFX, 11].PutValue(dr["E"].ToString());
cells[i + templateIndexDFX, 12].PutValue(dr["W"].ToString());
cells[i + templateIndexDFX, 13].PutValue(dr["O"].ToString());
cells[i + templateIndexDFX, 14].PutValue(dr["TEMPORARY_ACTION"].ToString());
cells[i + templateIndexDFX, 15].PutValue(dr["CORRECTIVE_PREVENTIVE_ACTION"].ToString());
cells[i + templateIndexDFX, 16].PutValue(dr["COMPLETE_DATE"].ToString().Length > 0 ? Convert.ToDateTime(dr["COMPLETE_DATE"].ToString()).ToString("yyyy/MM/dd") : dr["COMPLETE_DATE"].ToString());
cells[i + templateIndexDFX, 17].PutValue(dr["IMPROVEMENT_STATUS"].ToString());
}
}
#endregion
}
public static int GetEndCol(Aspose.Cells.Worksheet ws)
{
int iResult = 0;
while (true)
{
string sValue = ws.Cells[0, iResult].Value == null ? "" : ws.Cells[1, iResult].Value.ToString();
if (sValue != "")
{
iResult++;
}
else
{
break;
}
}
return(iResult);
}
protected void Button4_Click(object sender, EventArgs e)
{
DataTable dt = getdt();
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workBook.Worksheets[0];
if (dt.Rows.Count > 0)
{
sheet.Cells[0, 0].PutValue("PO单号");
sheet.Cells[0, 1].PutValue("商品编码");
sheet.Cells[0, 2].PutValue("商品名称");
sheet.Cells[0, 3].PutValue("需求日期");
sheet.Cells[0, 4].PutValue("下单日期");
sheet.Cells[0, 5].PutValue("数量");
sheet.Cells[0, 6].PutValue("单位");
sheet.Cells[0, 7].PutValue("预计发货日期");
sheet.Cells[0, 8].PutValue("发货备注");
sheet.Cells[0, 9].PutValue("联系人");
sheet.Cells[0, 10].PutValue("电话");
sheet.Cells[0, 11].PutValue("地址");
sheet.Cells[0, 12].PutValue("客服备注");
int j = 0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
j = i + 1;
sheet.Cells[j, 0].PutValue(dt.Rows[i]["CUST_PO_NUM"]);
sheet.Cells[j, 1].PutValue(dt.Rows[i]["ITEM_NUMBER"]);
sheet.Cells[j, 2].PutValue(dt.Rows[i]["ITEM_DESC"]);
sheet.Cells[j, 3].PutValue(dt.Rows[i]["NEED_BY_DATE"]);
sheet.Cells[j, 4].PutValue(dt.Rows[i]["CREATION_DATE"]);
sheet.Cells[j, 5].PutValue(dt.Rows[i]["QUANTITY"]);
sheet.Cells[j, 6].PutValue(dt.Rows[i]["UOM_CODE"]);
sheet.Cells[j, 7].PutValue(dt.Rows[i]["YJFHRQ"]);
sheet.Cells[j, 8].PutValue(dt.Rows[i]["Remark"]);
sheet.Cells[j, 9].PutValue(dt.Rows[i]["CONTACT_NAME"]);
sheet.Cells[j, 10].PutValue(dt.Rows[i]["PHONE_NUMBER"]);
sheet.Cells[j, 11].PutValue(dt.Rows[i]["ADDRESS"]);
sheet.Cells[j, 12].PutValue(dt.Rows[i]["DESCRIPTION"]);
}
workBook.Save(Server.MapPath("sfyorder" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls"));
DownLoadFile(Server.MapPath(""), "sfyorder" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls");
}
}
public void RectangleExport(DataTable table, Aspose.Cells.Worksheet xlSheet)
{
if (table.Rows.Count <= 0)
{
return;
}
//存放列头名称
ArrayList HeadList = new ArrayList();
//Aspose.Cells.Style style = new Aspose.Cells.Style();
//style.Font.Name = "宋体";
//style.ForegroundColor = Color.LightBlue;
//Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
//flag.Font = true;
//填写表头
for (int i = 0; i < table.Columns.Count; i++)
{
HeadList.Add(i);
xlSheet.Cells[0, i].PutValue(table.Columns[i].ColumnName);
}
//填写表内容
for (int Rowi = 0; Rowi < table.Rows.Count; Rowi++)
{
for (int Colj = 0; Colj < HeadList.Count; Colj++)
{
if (table.Rows[Rowi][Colj].ToString() != null)
{
xlSheet.Cells[Rowi + 1, Colj].PutValue(table.Rows[Rowi][Colj].ToString());
}
else
{
xlSheet.Cells[Rowi + 1, Colj].PutValue("");
}
}
}
//填写表格式
//xlSheet.Cells.ApplyStyle(style, flag);
}
public AsposeExcelCell(Aspose.Cells.Cell cell, Aspose.Cells.Worksheet worksheet)
{
if (cell == null)
{
return;
}
IsEmpty = cell.Type == Aspose.Cells.CellValueType.IsNull;
// nobody wants to know how excel represents numbers inside itself
// for "size_raw"
Text = cell.GetStringValue(Aspose.Cells.CellValueFormatStrategy.DisplayStyle);
if (Text == "###")
{
Text = cell.StringValue;
}
IsMerged = cell.IsMerged;
if (IsMerged)
{
FirstMergedRow = cell.GetMergedRange().FirstRow;
MergedRowsCount = cell.GetMergedRange().RowCount;
MergedColsCount = cell.GetMergedRange().ColumnCount;
}
else
{
MergedColsCount = 1;
MergedRowsCount = 1;
FirstMergedRow = cell.Row;
}
Row = cell.Row;
Col = cell.Column;
CellWidth = 0;
for (int i = 0; i < MergedColsCount; i++)
{
//test File17207: GetColumnWidthPixel returns 45, GetColumnWidth returns 0 for the same cell
CellWidth += (int)worksheet.Cells.GetColumnWidthPixel(cell.Column + i);
}
}
public static System.Data.DataTable GetDataFromExcelByCom(bool isFirstRowColumnName, string fileName)
{
System.Data.DataTable data = new System.Data.DataTable();
try
{
Aspose.Cells.Workbook workbook = null;
FileInfo fileInfo = new FileInfo(fileName);
if (fileInfo.Extension.ToLower().Equals(".xlsx"))
{
workbook = new Aspose.Cells.Workbook(fileName, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
}
else if (fileInfo.Extension.ToLower().Equals(".xls"))
{
workbook = new Aspose.Cells.Workbook(fileName, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Excel97To2003));
}
if (workbook != null)
{
Aspose.Cells.Worksheet worksheet = null;
worksheet = workbook.Worksheets[0];
if (worksheet != null)
{
data = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1,
isFirstRowColumnName);
return(data);
}
}
else
{
return(data);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(data);
}
private void BindIssuesList(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
//string logoPath = Page.Server.MapPath("") + "\\log.png";
//sheet.Pictures.Add(0, 0, 4, 10, logoPath);
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetIssuesInconformity(DocNo, "", "");
if (dtMaster.Rows.Count > 0)
{
int templateIndexDFX = 6; //模板row起始位置
int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
cells[i + templateIndexDFX, 1].PutValue(dr["Items"].ToString());
cells[i + templateIndexDFX, 2].PutValue(dr["STATION"].ToString());
cells[i + templateIndexDFX, 3].PutValue(dr["ISSUE_DESCRIPTION"].ToString());
cells[i + templateIndexDFX, 4].PutValue(dr["FILE_PATH"].ToString());
cells[i + templateIndexDFX, 5].PutValue(dr["ISSUE_LOSSES"].ToString());
cells[i + templateIndexDFX, 6].PutValue(dr["TEMP_MEASURE"].ToString());
cells[i + templateIndexDFX, 7].PutValue(dr["IMPROVE_MEASURE"].ToString());
cells[i + templateIndexDFX, 8].PutValue(dr["CURRENT_STATUS"].ToString());
cells[i + templateIndexDFX, 9].PutValue(dr["TRACKING"].ToString());
cells[i + templateIndexDFX, 10].PutValue(dr["REMARK"].ToString());
}
}
#endregion
}
public static DataTable ImportExcelToGrid(string pstrFilename, int iColEnd, int iRowNumber)
{
string mstr_FileName = pstrFilename;
string mstr_PathFileName = mstr_FileName;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(mstr_FileName);
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
DataTable dt = new DataTable();
int iRowEnd = GetEndRow(ws, iRowNumber + 1);
try
{
for (int j = 0; j < iColEnd; j++)
{
string strDataColumn = ws.Cells[iRowNumber - 1, j].Value.ToString().Trim();
dt.Columns.Add(new DataColumn(strDataColumn, typeof(string)));
}
int i = iRowNumber;
for (; i < iRowEnd; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < iColEnd; j++)
{
string strValue = ws.Cells[i, j].Value == null ? "" : ws.Cells[i, j].Value.ToString();
dr[j] = strValue;
}
dt.Rows.Add(dr);
}
return(dt);
}
catch (Exception exp)
{
}
return(dt);
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
if (!System.IO.Directory.Exists(dataDir))
{
System.IO.Directory.CreateDirectory(dataDir);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
int moduleIdx = workbook.VbaProject.Modules.Add(sheet);
Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[moduleIdx];
module.Codes =
"Sub ShowMessage()" + "\r\n" +
" MsgBox \"Welcome to Aspose!\"" + "\r\n" +
"End Sub";
Aspose.Cells.Drawing.Button button = sheet.Shapes.AddButton(2, 0, 2, 0, 28, 80);
button.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
button.Font.Name = "Tahoma";
button.Font.IsBold = true;
button.Font.Color = System.Drawing.Color.Blue;
button.Text = "Aspose";
button.MacroName = sheet.Name + ".ShowMessage";
workbook.Save(dataDir + "Output.out.xlsm");
Console.WriteLine("File saved");
//ExEnd:1
}
