// 设置执行公式计算 - 如果代码中用到公式，需要设置计算公式，导出的报表中，公式才会自动计算
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列的Range
Range 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
- Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
- wk.Open(file);
- Worksheet sht = wk.Worksheets[0];
- Cells cells = sht.Cells;
- if (sht==null)
- {
- return false;
- }
- int rowCount = cells.MaxDataRow+1;
-
- int cellCount = cells.MaxDataColumn + 1;
- string title = cells[j, k].Value.ToString();
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
wk.Open(file);//打开Excel文档
Worksheet sht = wk.Worksheets[0];//查看文档的sheet0内容
Cells cells = sht.Cells;//获取sheet0的所有单元格
if (sht==null)
{
return false;
}
int rowCount = cells.MaxDataRow+1;//当Excel没有一行数据时，读取到的cells.MaxDataRow=-1，当有一行数据时cells.MaxDataRow=0 MaxDataRow：包含数据的单元格的最大行索引
int cellCount = cells.MaxDataColumn + 1;//当Excel没有一行数据时，读取到的cells.MaxDataRow=-1，当有一行数据时cells.MaxDataRow=0 MaxDataRow：包含数据的单元格的最大列索引
string title = cells[j, k].Value.ToString();//获取第j行k列单元格的内容
写Excel
- int _BugNoColumn = cells.MaxDataColumn+1;;
- cells.InsertColumn(_BugNoColumn);
- Cell cell = cells[0, _BugNoColumn];
- cell.PutValue("abc");
//******************************************************************************************
上传
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>CxoBmxzcdRLLiQi1kzt5oSbz
9GhuyHHOBgjTf5w/wJ1V+lzjBYi8o7PvqRwkdQo4tT4dk
3PIJPbH9w5Lszei1SV/smkK8SCjR8kIWgLbOUFBvhD1
Fn9KgDAQ8B11psxIWvepKidw
8ZmDmbk9kdJbVBOkuAESXDdt
DEDZMB/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.IsSelectingLockedCellsAl
lowed = false;//设置只能选择解锁单元格
sheet.Protection.IsFormattingColumnsAllow
ed = 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 X
static 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
{
for (int j = 0
{
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> ///
