Aspose.Cells使用总结大全
生活随笔
收集整理的這篇文章主要介紹了
Aspose.Cells使用总结大全
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
使用到 Aspose.Cells 插件，整理一下。
一：新建解決方案，目錄如下
目錄說明：
Program.cs - 入口類
ExcelGenerator.cs - Aspose.Cells 操作類
Aspose.Cell.dll - 基礎dll【文件見文章底部源代碼內】
License.lic -?Aspose.Cells 破解證書【文件見文章底部源代碼內】
ps：由于 Aspose.Cells 插件 是收費插件，需要在使用插件前，設置一下許可證，否則在生成的Excel 中 會出現一個名叫 Evaluation Warning 的 Sheet.如圖所示：
二：Aspose.Cells 操作
2.1 引入?Aspose.Cell.dll?
2.2 設置?Aspose.Cell.dll 證書?License.lic
2.2.1 設置證書。我一般都寫在生成Excel類的構造函數中了。文件路徑要和證書的位置保持一致
Excel.License l = new Excel.License(); l.SetLicense("Aid/License.lic");2.2.2 修改證書屬性。在解決方案中，右擊?License.lic選擇屬性,修改?Copy to Ouput Directory 屬性為 Copy always
2.3 打開現有Execl 模板
//模板文件路徑 string Template_File_Path = @".\Template\Template.xlsx";// 打開 Excel 模板 Workbook CurrentWorkbook = File.Exists(Template_File_Path) ? new Workbook(Template_File_Path) : new Workbook();// 打開第一個sheet Worksheet DetailSheet = CurrentWorkbook.Worksheets[0];2.4 寫入數據
2.4.1 填寫數據到指定單元格
// 比如要在 A1 位置寫入 Demo這個值 Cell itemCell = DetailSheet.Cells["A1"];itemCell.PutValue("Demo");2.4.2 把DataTable寫入到Excel
// 獲取 Table 數據 DataTable dt = GetData();// 寫入數據的起始位置 string cell_start_region = "C1"; // 獲得開始位置的行號 int startRow = DetailSheet.Cells[cell_start_region].Row; // 獲得開始位置的列號 int startColumn = DetailSheet.Cells[cell_start_region].Column; // 寫入Excel。參數說明，直接查閱文章底部文檔鏈接 DetailSheet.Cells.ImportDataTable(dt, false, startRow, startColumn, true, true);2.5 保存Excel
// 設置執行公式計算 - 如果代碼中用到公式，需要設置計算公式，導出的報表中，公式才會自動計算 CurrentWorkbook.CalculateFormula(true);// 生成的文件名稱 string ReportFileName = string.Format("Excel_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd"));// 保存文件 CurrentWorkbook.Save(@".\Excel\" + ReportFileName, SaveFormat.Xlsx); ? ? ? /新建工作簿 Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells;//單元格sheet.Protect(ProtectionType.All, "123123", "");//保護工作表 sheet.Protection.IsSelectingLockedCellsAllowed = false;//設置只能選擇解鎖單元格 sheet.Protection.IsFormattingColumnsAllowed = true;//設置可以調整列 sheet.Protection.IsFormattingRowsAllowed = true;//設置可以調整行Style style1 = workbook.Styles[workbook.Styles.Add()];//新增樣式 style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中 style1.Font.Name = "宋體";//文字字體 style1.Font.Size = 22;//文字大小 style1.IsLocked = false;//單元格解鎖 style1.Font.IsBold = true;//粗體 style1.ForegroundColor = Color.FromArgb(0xaa, 0xcc, 0xbb);//設置背景色 style1.Pattern = BackgroundType.Solid; //設置背景樣式 style1.IsTextWrapped = true;//單元格內容自動換行 style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //應用邊界線 左邊界線 style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //應用邊界線 右邊界線 style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //應用邊界線 上邊界線style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //應用邊界線 下邊界線
cells.Merge(0, 0, 1, 5);//合并單元格 cells[0, 0].PutValue("內容");//填寫內容 cells[0, 0].SetStyle(style1);//給單元格關聯樣式cells.SetRowHeight(0, 20);//設置行高 cells.SetColumnWidth(1, 30);//設置列寬 cells[1, 0].Formula = "=AVERAGE(B1:E1)";//給單元格設置計算公式
//從Cells[0,0]開始創建一個2行3列的RangeRange range = ws.Cells.CreateRange(0, 0, 2, 3);Cell cell = range[0, 0];cell.Style.Font = 9;range.Style = style;range.Merge();
注意Range不能直接設置Style.必須先定義style再將style賦給Style.其他設置和Cell基本一致. Range的Style會覆蓋Cell定義的Style.另外必須先賦值再傳Style.否則可能不生效.
sheet.Cells[0,0].PutValue(1);sheet.Cells[1,0].PutValue(20);sheet.Cells[2,0].Formula="SUM(A1:B1)";sheet.CalculateFormula(true);Save Excel文件的時候必須調用CalculateFormula方法計算結果.
?
//********************************************************************************
1.創建execl（不需要服務器或者客戶端安裝office）
public void DCExexl(DataTable dt){Workbook wb = new Workbook();Worksheet ws = wb.Worksheets[0];Cells cell = ws.Cells;
?
?cell[0, 0].PutValue("ID");//添加數據到第0行和第0列
?cell.SetRowHeight(0, 0);設置行高
?Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];style1.HorizontalAlignment = TextAlignmentType.Right;//文字居中
?style1.Font.Name = "宋體";style1.Font.IsBold = true;//設置粗體style1.Font.Size = 12;//設置字體大小
?cell[0, 0].SetStyle(style1);
?cell.SetColumnWidth(0, 10.00);//列寬
Range range = cell.CreateRange(0, 0, 1, 1);//合并單元格 range.Merge();
?string FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";HttpResponse response = Page.Response;response.Buffer = true;response.Charset = "utf-8";response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);response.ContentEncoding = System.Text.Encoding.UTF8;response.ContentType = "application/ms-excel";response.BinaryWrite(wb.SaveToStream().ToArray());response.End();
}
2.讀取execl
public DataTable GetDataTable(string path){Workbook workbook = new Workbook();workbook.Open(path);Cells cells = workbook.Worksheets[0].Cells;DataTable dt = new DataTable();bool d = true;//防止表頭重復加載for (int i = 0; i < cells.MaxDataRow + 1; i++){DataRow row = dt.NewRow();for (int j = 0; j < cells.MaxDataColumn + 1; j++){if (d) {dt.Columns.Add(cells[0, j].StringValue.Trim()); }row[j] = cells[i + 1, j].StringValue.Trim();}dt.Rows.Add(row); d = false;}return dt;}
?
//*******************************************************************************
讀Excel
[csharp] view plain copy print??
寫Excel
[csharp] view plain copy print?//******************************************************************************************
?
?
上傳?
Workbook?Workbook?workBook?=?new?Workbook();?
屬性:?
?
| ?名稱 | ?值類型? | ?說明? |
| ?Colors? | ?Color[]? | ?獲取或設置Excel顏色? |
| ?ConvertNumericData? | ?bool? | ?獲取或設置是否將字符串轉換至數字數據? ?默認值為?true? |
| ?DataSorter? | ?DataSorter? | ?獲取或設置數據分級? |
| ?Date1904? | ?bool? | ? |
| ?DefaultStyle? | ?Aspose.Cells.Style? | ?獲取或設置工作簿默認樣式? |
| ?HasMacro? | ?bool? | ?獲取工作簿是否包含宏觀調控或宏? |
| ?IsHScrollBarVisible? | ?bool? | ?獲取或設置左部滾動條(控制行)? ?默認值為true? |
| ?IsProtected? | ?bool? | ?獲取工作簿保護狀態? |
| ?IsVScrollBarVisible? | ?bool? | ?獲取或設置底部滾動條(控制列)? ?默認值為true? |
| ? Language? | ? CountryCode?--枚舉類型? | ?獲取或設置語言? ?默認為當前計算機區域? |
| ?Password? | ?string? | ?獲取或設置工作簿密碼? |
| ?ReCalcOnOpen? | ?bool? | ?獲取或設置是否重新計算所有打開文件的公式? |
| ?Region? | ?CountryCode?--枚舉類型? | ?獲取或設置工作簿區域(指當前使用者區域)? ?默認為當前計算機區域? |
| ?Shared? | ?bool? | ?獲取或設置當前工作簿是否共享? ?默認為false? |
| ?ShowTabs? | ?bool? | ?獲取或設置是否顯示標簽（工作表標簽）? ?默認為true? |
| ?Styles? | ?Styles? | ?樣式集合? |
| ?Worksheets? | ?Worksheet? | ? |
?
事件:?
?
| ?CalculateFormula(bool?ignoreError? ?,ICustomFunction?customFunction)?+3? | ?void? | ?計算公式? |
| ?ChangePalette(Color?color,int?index)? | ?void? | ?設置當前顏色在調色版中顯示順序? |
| ?Combine(Workbook?secondWorkbook)? | ?void? | ?聯合工作簿,將secondWorkbook?工作簿中workSheet追加到當前工作簿中? |
| ?Copy(Workbook?source)? | ?void? | ?拷貝工作簿到當前工作簿? |
| ?Decrypt(string?password)? | ?void? | ?解除工作簿密碼? |
| ?IsColorInPalette(Color?color)? | ?bool? | ?將color加入到當前Excel調色版? |
| ?LoadData(string?fileName)?LoadData(System.IO.Stream?stream)? | ?void? | ?加載Excel到當前Workbook中? |
| ?Open(string?fileName,? ?FileFormatType.Default,? ?string?password?);?+8? | ?void? | ?打開Excel文件? |
| ?Protect(ProtectionType.All,? ?string?password)；? | ?void? | ?寫保護,并設置取消工作簿保護密碼? |
| ?RemoveExternalLinks()? | ?void? | ?移除外部鏈接? |
| ?RemoveMacro()? | ?void? | ?移除宏? |
| ?Replace?(string?PlaceHolder,? ?string?newValue);?+8? | ?void? | ?工作簿中類型和值完全符合的單元格，將其替換為新值或對象? |
| ?Save(Server.UrlEncode("測試.xls"),? ?FileFormatType.Default,? ?SaveType.OpenInExcel,?Response);+8? | ?Void? | ?保存工作簿? |
| ?SaveToStream()? | ?System. ? | ?將工作簿寫入內存流中? |
| ?Unprotect(string?password);? | ?Void? | ?取消工作簿保護狀態? |
| ?ValidateFormula(string?formula)? | ?bool? | ?驗證公式? |
?
-----------
using System; using System.Collections.Generic; using System.Text; using Aspose.Cells; using System.Data;namespace CRM.Common {public class AsposeExcel{private string outFileName = "";private Workbook book = null;private Worksheet sheet = null;private log4net.ILog log = log4net.LogManager.GetLogger(typeof(AsposeExcel));public AsposeExcel(string outfilename,string tempfilename){outFileName = outfilename;book = new Workbook();book.Open(tempfilename);sheet = book.Worksheets[0];}private void AddTitle(string title, int columnCount){sheet.Cells.Merge(0, 0, 1, columnCount);sheet.Cells.Merge(1, 0, 1, columnCount);Cell cell1 = sheet.Cells[0, 0];cell1.PutValue(title);cell1.Style.HorizontalAlignment = TextAlignmentType.Center;cell1.Style.Font.Name = "黑體";cell1.Style.Font.Size = 14;cell1.Style.Font.IsBold = true;Cell cell2 = sheet.Cells[1, 0];cell1.PutValue("查詢時間：" + DateTime.Now.ToLocalTime());cell2.SetStyle(cell1.Style);}private void AddHeader(DataTable dt){Cell cell = null;for (int col = 0; col < dt.Columns.Count; col++){cell = sheet.Cells[0, col];cell.PutValue(dt.Columns[col].ColumnName);cell.Style.Font.IsBold = true;}}private void AddBody(DataTable dt){for (int r = 0; r < dt.Rows.Count; r++){for (int c = 0; c < dt.Columns.Count; c++){sheet.Cells[r + 3, c].PutValue(dt.Rows[r][c].ToString());}}}public void DatatableToExcel(DataTable dt){try{//sheet.Name = sheetName;//AddTitle(title, dt.Columns.Count);//AddHeader(dt);AddBody(dt);sheet.AutoFitColumns();//sheet.AutoFitRows();book.Save(outFileName);}catch (Exception e){log.Error("導出Excel失敗！" + e.Message);throw e;}}} }導入就不說了。導入為datetable之后就自己操作就OK。
?
//********************************************************************************使用Aspose.Cells?設置Excel
步驟:??????? 1.WorkBookBase 繼承自Aspose.Cells.Workbook,在WorkBookBase 中注冊
??????? 2.使用WorkBookBase 操作Excel
??? /// <summary>
??? /// 創建workBook許可
??? /// </summary>
??? /// <author>wxl</author>
??? /// <date>2012-10-15</date>
??? public class WorkBookBase : Aspose.Cells.Workbook
??? {
??????? public WorkBookBase()
??????? {
??????????? Aspose.Cells.License license = new Aspose.Cells.License();
??????????? string strLic = @"<License>
????????????????????????????????? <Data>
??????????????????????????????????? <SerialNumber>aed83727-21cc-4a91-bea4-2607bf991c21</SerialNumber>
??????????????????????????????????? <EditionType>Enterprise</EditionType>
??????????????????????????????????? <Products>
????????????????????????????????????? <Product>Aspose.Total</Product>
??????????????????????????????????? </Products>
????????????????????????????????? </Data>
????????????????????????????????? <Signature>CxoBmxzcdRLLiQi1kzt5oSbz9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1Fn9KgDAQ8B11psxIWvepKidw8ZmDmbk9kdJbVBOkuAESXDdtDEDZMB/zL7Y=</Signature>
??????????????????????????????? </License>";
??????????? MemoryStream ms = new MemoryStream(System.Text.Encoding.ASCII.GetBytes(strLic));
??????????? license.SetLicense(ms);
?
??????? }?
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells;//單元格
sheet.Protect(ProtectionType.All, "123123", "");//保護工作表
sheet.Protection.IsSelectingLockedCellsAllowed = false;//設置只能選擇解鎖單元格
sheet.Protection.IsFormattingColumnsAllowed = true;//設置可以調整列
sheet.Protection.IsFormattingRowsAllowed = true;//設置可以調整行
Style style1 = workbook.Styles[workbook.Styles.Add()];//新增樣式
style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style1.Font.Name = "宋體";//文字字體
style1.Font.Size = 12;//文字大小
style1.IsLocked = false;//單元格解鎖
style1.Font.IsBold = true;//粗體
style1.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);//設置背景色
style1.Pattern = BackgroundType.Solid; //設置背景樣式
style1.IsTextWrapped = true;//單元格內容自動換行
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //應用邊界線 左邊界線
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //應用邊界線 右邊界線
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //應用邊界線 上邊界線
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //應用邊界線 下邊界線 //設置單元格背景顏色
style1.ForegroundColor?=?System.Drawing.Color.FromArgb(153,?204,?0);
style1.Pattern?=?BackgroundType.Solid;
cells.Merge(0, 0, 1, 5);//合并單元格
cells[0, 0].PutValue("內容");//填寫內容
cells[0, 0].SetStyle(style1);//給單元格關聯樣式//cells[0,0].Style=style1;//給單元格關聯樣式
?????????????????????? ?
cells.SetRowHeight(0, 38);//設置行高
cells.SetColumnWidth(1, 20);//設置列寬
? cells[1, 0].Formula = "=AVERAGE(B1:E1)";//給單元格設置計算公式
? System.IO.MemoryStream ms = workbook.SaveToStream();//生成數據流
? byte[] bt = ms.ToArray();
? workbook.Save(@"D:\test.xls");//保存到硬盤
?
//***********************************************************************************************
基于Aspose.Cells.dll 封裝了對于導出的Excel的各種樣式設置，內容填充操作，目前支持邊框樣式，顏色，字體，合并單元格等操作,簡化Aspose.Cells.dll的使用
調用示例
/// ---------->Clom Y/// |/// |/// |/// \/ Row Xstatic void Main(string[] args){object[] clom = { "列名1", "列名2", "列名3" };object[] row = { "行名1", "行名2", "行名3", "行名4" };String filename = "text.xlsx";//列標題樣式CellStyle Styleclom = new CellStyle();Styleclom.AllBorder = Aspose.Cells.CellBorderType.Thin;Styleclom.ForegroundColor = Color.Yellow;Styleclom.IsBold = true;//行標題樣式CellStyle Stylerow = new CellStyle();Stylerow.AllBorder = Aspose.Cells.CellBorderType.Thin;Stylerow.ForegroundColor = Color.ForestGreen;Stylerow.IsBold = true;//單元格樣式CellStyle Stylebody = new CellStyle();Stylebody.AllBorder = Aspose.Cells.CellBorderType.Medium;Stylebody.ForegroundColor = Color.LightBlue;Stylebody.IsBold = true;Stylebody.IsItalic = true;//將樣式和內容填充到模板中ExcelFormat eformat = new ExcelFormat();eformat.SavePath = filename;eformat.ColumnsSize = 20;eformat.RowsSize = 20;//直接插入標題//eformat.InsertTitle(clom.ToList(), Styleclom, ExcelFormat.TitleType.列標題);//eformat.InsertTitle(row.ToList(), Stylerow, ExcelFormat.TitleType.行標題);eformat.InsertCellRow(new CellRow(1, 4, 0, clom.ToList()), Stylerow);eformat.InsertCellColm(new CellColm(1, 5, 0, row.ToList()), Styleclom);for (int i = 0; i < clom.Length; i++){for (int j = 0; j < row.Length; j++){SCell scell = new SCell();scell.Txt_Obj = Convert.ToString(row[j]) + Convert.ToString(row[i]);scell.X = j + 1;scell.Y = i + 1;scell.CStyle = Stylebody;eformat.SCells.Add(scell);}}//向Excel中寫入數據ExcelMethod.InsertData(eformat, true);Console.WriteLine("完畢");Console.ReadLine();}導出例子
GitHub地址
2017/11/15更新后 不再對所謂的標題行標題列作區分(在ExcelFormat對象中只保留SCells屬性，即可配置樣式的單元格集合。除此之外，新增了數據行，數據列，數據區塊的概念，方便一組規則且具有相同樣式的數據區塊插入。為確保配置樣式和插入的靈活性，所有的單元格最終匯總到SCells中等待寫入)
//********************************************************************************
Aspose.cell.dll的使用，導excel表
?
using?System; using?System.Web; using?EF; using?Newtonsoft.Json; using?System.Collections.Generic; using?System.Linq; using?System.IO; using?Aspose.Cells; //using?Microsoft.Office.Interop.Excel; //using?System.Reflection; public?class?ToOverTimexls?:?IHttpHandler { ????public?void?ProcessRequest(HttpContext?context) ????{ ????????int?oname?=?0,?years?=?0,?month?=?0; ????????if?(context.Request["name"]?!=?null) ????????{ ????????????oname?=?int.Parse(context.Request["name"]); ????????} ????????if?(context.Request["years"]?!=?null) ????????{ ????????????years?=?int.Parse(context.Request["years"]); ????????} ????????if?(context.Request["month"]?!=?null) ????????{ ????????????month?=?int.Parse(context.Request["month"]); ????????} ????????//oname?=?1;?years?=?2016;?month?=?1; ????????using?(WorkRecordEntities?db?=?new?WorkRecordEntities()) ????????{ ????????????IList<OverTime>?list?=?db.OverTime.Where(o?=>?o.StaffID?==?oname?&&?o.StartTime.Year?==?years?&&?o.StartTime.Month?==?month).ToList(); ????????????var?name?=?db.Staff.Where(o?=>?o.StaffID?==?oname).FirstOrDefault().FullName; ????????????//建立一個Excel進程?Application ????????????//?string?SavaFilesPath?=?System.Configuration.ConfigurationManager.AppSettings["DownLoad"]?+?Guid.NewGuid()?+?".xls"; ????????????string?SavaFilesPath?=?context.Server.MapPath("~/Download")?+?"\\"?+?Guid.NewGuid()?+?".xls"; ????????????//?Application?excelApplication?=?new?Application(); ????????????//?//默認值為?True。如果不想在宏運行時被無窮無盡的提示和警告消息所困擾，請將本屬性設置為?False；這樣每次出現需用戶應答的消息時，Microsoft?Excel ????????????//?//?將選擇默認應答。 ????????????//?//如果將該屬性設置為?False，則在代碼運行結束后，Micorosoft?Excel?將該屬性設置為?True，除非正運行交叉處理代碼。 ????????????//?//如果使用工作簿的?SaveAs?方法覆蓋現有文件，“覆蓋”警告默認為“No”，當?DisplayAlerts?屬性值設置為?True?時，Excel?選擇“Yes”。 ????????????//?excelApplication.DisplayAlerts?=?false; ????????????//?//??建立或打開一個?Workbook對象生成新Workbook ????????????//?Workbook?workbook?=?excelApplication.Workbooks.Add(Missing.Value); ????????????//?//int?x?=?2; ????????????//?Worksheet?lastWorksheet?=?(Worksheet)workbook.Worksheets.get_Item(workbook.Worksheets.Count); ????????????//?Worksheet?newSheet?=?(Worksheet)workbook.Worksheets.Add(Type.Missing,?lastWorksheet,?Type.Missing,?Type.Missing); ????????????//?//表頭 ????????????//?newSheet.Cells[4,?1]?=?"No.(序號)"; ????????????//?newSheet.Cells[4,?2]?=?"Date(日期)"; ????????????//?newSheet.Cells[4,?3]?=?"Mon.～Sun."; ????????????//?newSheet.Cells[4,?4]?=?"From(開始)"; ????????????//?newSheet.Cells[4,?5]?=?"To(止)"; ????????????//?newSheet.Cells[4,?6]?=?"OT?Hrs.(時間)"; ????????????//?newSheet.Cells[4,?7]?=?"Evnt(加班事由)"; ????????????//?newSheet.Cells[4,?8]?=?"Approve(審批)"; ????????????//?newSheet.Cells[3,?4]?=?"To(加班時間)"; ????????????//?newSheet.Cells[1,4]="加班申請表"; ????????????//?newSheet.Cells[2,1]="部門:后臺"; ?????????????newSheet.Cells[2,?2]?=?"后臺"; ????????????//?newSheet.Cells[2,?6]?=?"姓名:"+name; ????????????//?//newSheet.Cells[2,?7]?=?name; ????????????//?newSheet.get_Range("A1",?"H1").Merge(newSheet.get_Range("A1",?"H1").MergeCells); ????????????//?newSheet.get_Range("A2",?"D2").Merge(newSheet.get_Range("A2",?"D2").MergeCells); ????????????//?newSheet.get_Range("F2",?"H2").Merge(newSheet.get_Range("F2",?"H2").MergeCells); ????????????//?newSheet.get_Range("A3",?"A4").Merge(newSheet.get_Range("A3",?"A4").MergeCells); ????????????//?newSheet.get_Range("B3",?"B4").Merge(newSheet.get_Range("B3",?"B4").MergeCells); ????????????//?newSheet.get_Range("C3",?"C4").Merge(newSheet.get_Range("C3",?"C4").MergeCells); ????????????//?newSheet.get_Range("D3",?"F3").Merge(newSheet.get_Range("D3",?"F3").MergeCells); ????????????//?newSheet.get_Range("G3",?"G4").Merge(newSheet.get_Range("G3",?"G4").MergeCells); ????????????//?newSheet.get_Range("H3",?"H4").Merge(newSheet.get_Range("H3",?"H4").MergeCells); ????????????//?newSheet.get_Range("A3",?"H3").Interior.ColorIndex?=?15; ????????????//?newSheet.get_Range("A4",?"H4").Interior.ColorIndex?=?15; ????????????//?Range?range1?=?newSheet.get_Range("A1",?"H1"); ????????????//?range1.HorizontalAlignment?=?XlHAlign.xlHAlignCenter; ????????????//?range1.WrapText?=?true; ????????????//?Range?range?=?newSheet.get_Range("A3",?"H4"); ????????????//?range.HorizontalAlignment?=?XlHAlign.xlHAlignCenter; ????????????//?range.Font.Size?=?10; ????????????//?range.Borders.LineStyle?=?1; ????????????//?//設置邊框???????? ?????????????range.BorderAround(XlLineStyle.xlContinuous,?XlBorderWeight.xlMedium,?XlColorIndex.xlColorIndexAutomatic,?System.Drawing.Color.Black.ToArgb());???????? ????????????//?range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight?=?XlBorderWeight.xlMedium; ????????????//?range.WrapText?=?true; ?????????????var?x?=?3; ????????????//?var?x?=?5; ????????????//?for?(var?i?=?0;?i?<?list.Count;?i++) ????????????//?{ ????????????//?????newSheet.Cells[x?+?i,?1]?=?i?+?1; ????????????//?????newSheet.Cells[x?+?i,?2]?=?list[i].StartTime.Month?+?"月"?+?list[i].StartTime.Day?+?"日"; ????????????//?????newSheet.Cells[x?+?i,?3]?=?GetWeekCHA((list[i].StartTime.DayOfWeek).ToString()); ????????????//?????newSheet.Cells[x?+?i,?4]?=?list[i].StartTime.ToString("yyyy/MM/dd?HH:mm:ss"); ????????????//?????newSheet.Cells[x?+?i,?5]?=?list[i].EndTime.ToString("yyyy/MM/dd?HH:mm:ss"); ????????????//?????int?ts?=?(list[i].EndTime?-?list[i].StartTime).Hours; ????????????//?????newSheet.Cells[x?+?i,?6]?=?ts; ????????????//?????newSheet.Cells[x?+?i,?7]?=?list[i].Description; ????????????//?????newSheet.Cells[x?+?i,?8]?=?""; ????????????//?} ????????????//?newSheet.Cells.Columns.AutoFit(); ????????????//?//刪除原來的空Sheet ????????????//?((Worksheet)workbook.Worksheets.get_Item(1)).Delete(); ????????????//?((Worksheet)workbook.Worksheets.get_Item(1)).Delete(); ????????????//?((Worksheet)workbook.Worksheets.get_Item(1)).Delete(); ????????????//?//設置默認選中是第一個Sheet?類似于Select(); ????????????//?((Worksheet)workbook.Worksheets.get_Item(1)).Activate(); ????????????//?try ????????????//?{ ????????????//?????workbook.Close(true,?SavaFilesPath,?Missing.Value); ????????????//?} ????????????//?catch?(Exception?e) ????????????//?{ ????????????//?????throw?e; ????????????//?} ????????????//?UploadExcel(SavaFilesPath,?true); ????????????//?excelApplication.Quit(); ????????????Workbook?workbook?=?new?Workbook(); ????????????Worksheet?worksheet?=?workbook.Worksheets[0]; ????????????Style?styleTitle?=?workbook.Styles[workbook.Styles.Add()];//新增樣式??? ????????????styleTitle.HorizontalAlignment?=?TextAlignmentType.Center;//文字居中 ????????????worksheet.PageSetup.Orientation?=?PageOrientationType.Landscape;//橫向打印 ????????????worksheet.PageSetup.Zoom?=?100;//以100%的縮放模式打開 ????????????worksheet.PageSetup.PaperSize?=?PaperSizeType.PaperA4; ????????????Range?range;?Cell?cell; ????????????range?=?worksheet.Cells.CreateRange(0,?0,?1,?8); ????????????range.Merge(); ????????????range.RowHeight?=?20; ????????????range.ColumnWidth?=?15; ????????????cell?=?range[0,?0]; ????????????cell.PutValue("加班申請表"); ????????????cell.SetStyle(styleTitle); ????????????range?=?worksheet.Cells.CreateRange(1,?0,?1,?2); ????????????range.Merge(); ????????????range.RowHeight?=?15; ????????????cell?=?range[0,?0]; ????????????cell.PutValue("部門：后臺"); ????????????range?=?worksheet.Cells.CreateRange(1,?4,?1,?2); ????????????range.Merge(); ????????????range.RowHeight?=?15; ????????????cell?=?range[0,?0]; ????????????cell.PutValue("姓名："?+?name); ????????????//range?=?worksheet.Cells.CreateRange(1,?5,?1,?1); ????????????//range.Merge(); ????????????//range.RowHeight?=?15; ????????????//cell?=?range[0,?0]; ????????????//cell.PutValue("方亭"); ????????????range?=?worksheet.Cells.CreateRange(2,?0,?2,?1); ????????????range.Merge(); ????????????cell?=?range[0,?0]; ????????????cell.PutValue("No.(序號)"); ????????????cell.SetStyle(styleTitle); ????????????range?=?worksheet.Cells.CreateRange(2,?1,?2,?1); ????????????range.Merge(); ????????????cell?=?range[0,?0]; ????????????cell.PutValue("Date(日期)"); ????????????range?=?worksheet.Cells.CreateRange(2,?2,?2,?1); ????????????range.Merge(); ???????????? ????????????cell?=?range[0,?0]; ????????????cell.PutValue("Mon.～Sun."); ????????????cell.SetStyle(styleTitle); ????????????range?=?worksheet.Cells.CreateRange(2,?3,?1,?3); ????????????range.Merge(); ????????????range.ColumnWidth?=?20; ???????????? ????????????cell?=?range[0,?0]; ????????????cell.PutValue("To(加班時間)"); ????????????cell.SetStyle(styleTitle); ????????????cell?=?worksheet.Cells[3,?3]; ????????????cell.PutValue("From(開始)"); ????????????cell.SetStyle(styleTitle); ????????????cell?=?worksheet.Cells[3,?4]; ????????????cell.PutValue("To(止)"); ????????????cell.SetStyle(styleTitle); ????????????cell?=?worksheet.Cells[3,?5]; ????????????cell.PutValue("OT?Hrs.(時間)"); ????????????cell.SetStyle(styleTitle); ????????????range?=?worksheet.Cells.CreateRange(2,?6,?2,?1); ????????????range.Merge(); ????????????cell?=?range[0,?0]; ????????????cell.PutValue("Evnt(加班事由)"); ????????????cell.SetStyle(styleTitle); ????????????range?=?worksheet.Cells.CreateRange(2,?7,?2,?1); ????????????range.Merge(); ????????????cell?=?range[0,?0]; ????????????cell.PutValue("Approve(審批)"); ????????????cell.SetStyle(styleTitle); ????????????for?(var?i?=?0;?i?<?list.Count;?i++) ????????????{ ????????????????//newSheet.Cells[x?+?i,?1]?=?i?+?1; ????????????????//newSheet.Cells[x?+?i,?2]?=?list[i].StartTime.Month?+?"月"?+?list[i].StartTime.Day?+?"日"; ????????????????//newSheet.Cells[x?+?i,?3]?=?GetWeekCHA((list[i].StartTime.DayOfWeek).ToString()); ????????????????//newSheet.Cells[x?+?i,?4]?=?list[i].StartTime.ToString("yyyy/MM/dd?HH:mm:ss"); ????????????????//newSheet.Cells[x?+?i,?5]?=?list[i].EndTime.ToString("yyyy/MM/dd?HH:mm:ss"); ????????????????//int?ts?=?(list[i].EndTime?-?list[i].StartTime).Hours; ????????????????//newSheet.Cells[x?+?i,?6]?=?ts; ????????????????//newSheet.Cells[x?+?i,?7]?=?list[i].Description; ????????????????//newSheet.Cells[x?+?i,?8]?=?""; ????????????????cell?=?worksheet.Cells[4?+?i,?0]; ????????????????cell.PutValue(i?+?1); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?1]; ????????????????cell.PutValue(list[i].StartTime.Month?+?"月"?+?list[i].StartTime.Day?+?"日"); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?2]; ????????????????cell.PutValue(GetWeekCHA((list[i].StartTime.DayOfWeek).ToString())); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?3]; ????????????????cell.PutValue(list[i].StartTime.ToString("yyyy/MM/dd?HH:mm:ss")); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?4]; ????????????????cell.PutValue(list[i].EndTime.ToString("yyyy/MM/dd?HH:mm:ss")); ????????????????cell.SetStyle(styleTitle); ????????????????int?ts?=?(list[i].EndTime?-?list[i].StartTime).Hours; ????????????????cell?=?worksheet.Cells[4?+?i,?5]; ????????????????cell.PutValue(ts); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?6]; ????????????????cell.PutValue(list[i].Description); ????????????????cell.SetStyle(styleTitle); ????????????????cell?=?worksheet.Cells[4?+?i,?7]; ????????????????cell.PutValue(""); ????????????????cell.SetStyle(styleTitle); ????????????} ????????????workbook.Save(SavaFilesPath); ????????????UploadExcel(SavaFilesPath,?true); ????????} ????} ????///???<summary>??? ????///???返回星期中文名??? ????///???</summary>??? ????///???<param???name="WeekENG">星期英文名</param>??? ????///???<returns></returns>??? ????public?string?GetWeekCHA(string?WeekENG) ????{ ????????string?return_value?=?""; ????????switch?(WeekENG) ????????{ ????????????case?"Monday": ????????????????return_value?=?"星期一"; ????????????????return?return_value; ????????????case?"Tuesday": ????????????????return_value?=?"星期二"; ????????????????return?return_value; ????????????case?"Wednesday": ????????????????return_value?=?"星期三"; ????????????????return?return_value; ????????????case?"Thursday": ????????????????return_value?=?"星期四"; ????????????????return?return_value; ????????????case?"Friday": ????????????????return_value?=?"星期五"; ????????????????return?return_value; ????????????case?"Saturday": ????????????????return_value?=?"星期六"; ????????????????return?return_value; ????????????case?"Sunday": ????????????????return_value?=?"星期日"; ????????????????return?return_value; ????????} ????????return?return_value; ????} ????///?<summary> ????///?提供下載 ????///?</summary> ????///?<param?name="path"></param> ????///?<param?name=