public static void Run()
{
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindingCellsWithStringOrNumber.xlsx");
workbook.CalculateFormula();
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
// Find the cell with the input integer or double
Cell cell1 = cells.Find(224, null, opts);
if (cell1 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell1.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// Find the cell with the input string
Aspose.Cells.Cell cell2 = cells.Find("Items E", null, opts);
if (cell2 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell2.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// Find the cell containing with the input string
opts.LookAtType = LookAtType.Contains;
Cell cell3 = cells.Find("Data", null, opts);
if (cell3 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell3.Name);
}
else
{
Console.WriteLine("Record not found ");
}
Console.WriteLine("FindingCellsWithStringOrNumber executed successfully.");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate the workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
//Find the cell with the input integer or double
Cell cell1 = cells.Find(205, null, opts);
if (cell1 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell1.Name);
}
else
{
Console.WriteLine("Record not found ");
}
//Find the cell with the input string
Aspose.Cells.Cell cell2 = cells.Find("Items A", null, opts);
if (cell2 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell2.Name);
}
else
{
Console.WriteLine("Record not found ");
}
//Find the cell containing with the input string
opts.LookAtType = LookAtType.Contains;
Cell cell3 = cells.Find("Data", null, opts);
if (cell3 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell3.Name);
}
else
{
Console.WriteLine("Record not found ");
}
}
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);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a Workbook.
Workbook workbook = new Workbook();
//Get cells collection in the first (default) worksheet.
Cells cells = workbook.Worksheets[0].Cells;
//Get the D3 cell.
Aspose.Cells.Cell c = cells["D3"];
//Get the style of the cell.
Style s = c.GetStyle();
//Set foreground color for the cell from the default theme Accent2 color.
s.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent2, 0.5);
//Set the pattern type.
s.Pattern = BackgroundType.Solid;
//Get the font for the style.
Aspose.Cells.Font f = s.Font;
//Set the theme color.
f.ThemeColor = new ThemeColor(ThemeColorType.Accent4, 0.1);
//Apply style.
c.SetStyle(s);
//Put a value.
c.PutValue("Testing1");
//Save the excel file.
workbook.Save(dataDir + "output.out.xlsx");
//ExEnd:1
}
private Aspose.Cells.Cell WriteCell(int irow, int icol, object obj, int dq)
{
Aspose.Cells.Cell cl = sheet.Cells[irow, icol];
cl.PutValue(obj);
switch (dq)
{
case 1:
cl.Style.HorizontalAlignment = TextAlignmentType.Center;
break;
case 2:
cl.Style.HorizontalAlignment = TextAlignmentType.Left;
break;
}
cl.Style = st;
return(cl);
}
public Aspose.Cells.Workbook GetExcel()
{
// Instantiate a Workbook object that represents Excel file.
Workbook wb = new Workbook();
// When you create a new workbook, a default "Sheet1" is added to the workbook.
Worksheet sheet = wb.Worksheets[0];
// Access the "A1" cell in the sheet.
Aspose.Cells.Cell cell = sheet.Cells["A1"];
// Input the "Hello World!" text into the "A1" cell
cell.PutValue("Hello World!");
// Save the Excel file.
return(wb);
}
static void Main(string[] args)
{
//Instantiate a new Workbook object.
Workbook workbook = new Workbook();
//Get the First sheet.
Worksheet worksheet = workbook.Worksheets[0];
//Define A1 Cell.
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
//Add a hyperlink to it.
int index = worksheet.Hyperlinks.Add("A1", 1, 1, "http://www.aspose.com/");
worksheet.Hyperlinks[index].TextToDisplay = "Aspose Site!";
worksheet.Hyperlinks[index].ScreenTip = "Click to go to Aspose site";
//Save the excel file.
workbook.Save("Hyperlink_test.xls");
}
public CellValue checkData(int v_col, EXCEL.Cell v_cellData)
{
ExcelHeaderDecorate ehd = m_header_dct[v_col];
string type = ehd.Type;
ECellDataStructure ds = ehd.DataStructure;
CellValue val = null;
object data = v_cellData.Value;
bool success = false;
if (data == null || string.IsNullOrEmpty(data.ToString()))
{
if (!ehd.CanBeEmpty)
{
Debug.Exception("不可为空");
return(new MissVal());
}
}
switch (ds)
{
case ECellDataStructure.single:
val = CellValue.CheckCellVal(ehd);
success = val.Init(v_cellData, ehd.get_default(), ehd.get_constraint());
break;
case ECellDataStructure.array:
val = new ListVal(type);
//val.FieldType = type;
val.IsStretch = ehd.IsStretch;
success = val.Init(v_cellData, ehd.get_default(), ehd.get_constraint());
break;
}
if (success)
{
val.IsStretch = ehd.IsStretch;
return(val);
}
else
{
val = new MissVal();
success = val.Init("[nil]");
Debug.Exception("没有找到名为{0}类型为{1}的ID", v_cellData.Value, type);
return(null);
}
}
//Quang Huy 2014-02-19
/// <summary>
/// Tạo viền cho ô
/// </summary>
/// <param name="cell"></param>
/// <param name="position"></param>
/// <param name="color"></param>
private bool SetCellBorder(Aspose.Cells.Cell cell, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
{
try
{
System.Drawing.Color c = color ?? System.Drawing.Color.Black;
Aspose.Cells.Style style = cell.GetStyle();
if (((int)position & (int)BorderPosition.Top) == (int)BorderPosition.Top)
{
style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.TopBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Right) == (int)BorderPosition.Right)
{
style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.RightBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Bottom) == (int)BorderPosition.Bottom)
{
style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = c;
}
if (((int)position & (int)BorderPosition.Left) == (int)BorderPosition.Left)
{
style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = c;
}
if (((int)position & (int)BorderPosition.DiagonalDown) == (int)BorderPosition.DiagonalDown)
{
style.Borders[Aspose.Cells.BorderType.DiagonalDown].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.DiagonalDown].Color = c;
}
if ((((int)position & (int)BorderPosition.DiagonalUp) == (int)BorderPosition.DiagonalUp) || (position == BorderPosition.Full))
{
style.Borders[Aspose.Cells.BorderType.DiagonalUp].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.DiagonalUp].Color = c;
}
//Apply the border styles to the cell
cell.SetStyle(style);
return(true);
}
catch { }
return(false);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Clearing all the worksheets
workbook.Worksheets.Clear();
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Setting the vertical alignment of the text in a cell
style.VerticalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
// Obtaining the style of the cell
Style style = cell.GetStyle();
// ExStart:SetSubscript
// Setting subscript effect
style.Font.IsSubscript = true;
// ExEnd:SetSubscript
// Applying the style to the cell
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
static void Main(string[] args)
{
// Tracing Precedents
//Instantiating a Workbook object
Workbook workbook = new Workbook("C:\\book1.xls");
Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Cell cell = cells["B7"];
//Tracing precedents of the cell B7.
//The return array contains ranges and cells.
ReferredAreaCollection ret = cell.GetPrecedents();
//Printing all the precedent cells' name.
if (ret != null)
{
for (int m = 0; m < ret.Count; m++)
{
ReferredArea area = ret[m];
StringBuilder stringBuilder = new StringBuilder();
if (area.IsExternalLink)
{
stringBuilder.Append("[");
stringBuilder.Append(area.ExternalFileName);
stringBuilder.Append("]");
}
stringBuilder.Append(area.SheetName);
stringBuilder.Append("!");
stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
if (area.IsArea)
{
stringBuilder.Append(":");
stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
}
Console.WriteLine(stringBuilder.ToString());
}
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
//Obtaining the style of the cell
Style style = cell.GetStyle();
//Setting the font to be underlined
style.Font.Underline = FontUnderlineType.Single;
//Applying the style to the cell
cell.SetStyle(style);
//Saving the Excel file
workbook.Save(dataDir + "book1.xls", SaveFormat.Excel97To2003);
}
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);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
//Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
//Setting the indentation level of the text (inside the cell) to 2
style.IndentLevel = 2;
cell.SetStyle(style);
//Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
//ExEnd:1
}
public virtual bool Init(EXCEL.Cell v_cellData, string v_default = null, string[] v_constraint = null)
{
string strVal;
if (v_cellData.Value == null || string.IsNullOrEmpty(v_cellData.StringValue))
{
if (!string.IsNullOrEmpty(v_default))
{
strVal = v_default;
}
else
{
_isMiss = true;
return(true);
}
}
else
{
strVal = v_cellData.Value.ToString();
}
return(Init(strVal, v_constraint));
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
//Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
//Setting the rotation of the text (inside the cell) to 25
style.RotationAngle = 25;
cell.SetStyle(style);
//Saving the Excel file
workbook.Save(dataDir + "book1.xls", SaveFormat.Excel97To2003);
}
//Quang Huy 2014-02-19
/// <summary>
/// Thực hiện set value cho cell
/// </summary>
/// <param name="cellName">Tên của ô (VD "B6")</param>
/// <param name="value">Giá trị</param>
public bool WriteToCell(string cellName, object value)
{
Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
return(WriteToCell(cell.Row, cell.Column, value));
}
//Quang Huy 2014-02-19
/// <summary>
/// Thực hiện đặt viền cho ô
/// </summary>
/// <param name="rowIndex">chỉ số dòng (dòng 1 chỉ số là 0)</param>
/// <param name="colIndex">chỉ số cột (cột A chỉ số là 1)</param>
/// <param name="position">Vị trí viền (mặc định viền xung quanh)</param>
/// <param name="color">màu viền (mặc định đen)</param>
/// <returns></returns>
public bool SetCellBorder(int rowIndex, int colIndex, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
{
Aspose.Cells.Cell cell = _worksheet.Cells[rowIndex, colIndex];
return(SetCellBorder(cell, position, color));
}
/// <summary>
/// FreezePanes
/// </summary>
/// <param name="cellName">Tên cột</param>
/// <param name="freezedRow"></param>
/// <param name="freezedCol"></param>
public void FreezePanes(string cellName)
{
Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
_worksheet.FreezePanes(cellName, cell.Row, cell.Column);
}
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);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiate a new workbook
Workbook workbook = new Workbook();
//Get the cells in the first(default) worksheet
Cells cells = workbook.Worksheets[0].Cells;
//Get the A1 cell
Aspose.Cells.Cell p = cells["A1"];
//Enter a value
p.PutValue("Preface");
//Get the A10 cell
Aspose.Cells.Cell A = cells["A10"];
//Enter a value.
A.PutValue("page1");
//Get the H15 cell
Aspose.Cells.Cell D = cells["H15"];
//Enter a value
D.PutValue("page1(H15)");
//Add a new worksheet to the workbook
workbook.Worksheets.Add();
//Get the cells in the second sheet
cells = workbook.Worksheets[1].Cells;
//Get the B10 cell in the second sheet
Aspose.Cells.Cell B = cells["B10"];
//Enter a value
B.PutValue("page2");
//Add a new worksheet to the workbook
workbook.Worksheets.Add();
//Get the cells in the third sheet
cells = workbook.Worksheets[2].Cells;
//Get the C10 cell in the third sheet
Aspose.Cells.Cell C = cells["C10"];
//Enter a value
C.PutValue("page3");
//Create a main PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry pbeRoot = new Aspose.Cells.Rendering.PdfBookmarkEntry();
//Specify its text
pbeRoot.Text = "Sections";
//Set the destination cell/location
pbeRoot.Destination = p;
//Set its sub entry array list
pbeRoot.SubEntry = new ArrayList();
//Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe1 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
//Specify its text
subPbe1.Text = "Section 1";
//Set its destination cell
subPbe1.Destination = A;
//Define/Create a sub Bookmark entry object of "Section A"
Aspose.Cells.Rendering.PdfBookmarkEntry ssubPbe = new Aspose.Cells.Rendering.PdfBookmarkEntry();
//Specify its text
ssubPbe.Text = "Section 1.1";
//Set its destination
ssubPbe.Destination = D;
//Create/Set its sub entry array list object
subPbe1.SubEntry = new ArrayList();
//Add the object to "Section 1"
subPbe1.SubEntry.Add(ssubPbe);
//Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe1);
//Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe2 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
//Specify its text
subPbe2.Text = "Section 2";
//Set its destination
subPbe2.Destination = B;
//Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe2);
//Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe3 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
//Specify its text
subPbe3.Text = "Section 3";
//Set its destination
subPbe3.Destination = C;
//Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe3);
//Create an instance of PdfSaveOptions
Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
//Set the PDF Bookmark root object
pdfSaveOptions.Bookmark = pbeRoot;
//Save the pdf file
workbook.Save(dataDir + "PDFBookmarks_test.out.pdf", pdfSaveOptions);
//ExEnd:1
}
public ActionResult Export(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "rownum idx";
pagination.p_fields = @"equipmentname,purpose,
(Concat(equipone,equipunitone)) as equipone,
equipratioone,
(Concat(practicalequipone,practicalequipunitone)) as practicalequipone,
(Concat(equiptwo,equipunittwo)) as equiptwo,
equipratiotwo,
(Concat(practicalequiptwo,practicalequipunittwo)) as practicalequiptwo";
pagination.p_tablename = "HRS_FIREEQUIP t";
pagination.conditionJson = string.Format(" 1=1 ");
pagination.sidx = "createdate"; //排序字段
pagination.sord = "desc"; //排序方式
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
pagination.conditionJson += string.Format(" and CREATEUSERORGCODE ='{0}'", user.OrganizeCode);
}
var watch = CommonHelper.TimerStart();
var exportTable = FireEquipbll.GetPageList(pagination, queryJson);
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "基本防护装备配备标准";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "基本防护装备配备标准.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "idx", ExcelColumn = "序号", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipmentname", ExcelColumn = "名称", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "purpose", ExcelColumn = "主要用途", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipone", ExcelColumn = "一级站配备", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipratioone", ExcelColumn = "一级站备份比", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "practicalequipone", ExcelColumn = "一级站实际配备数量", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equiptwo", ExcelColumn = "二级站配备", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "equipratiotwo", ExcelColumn = "二级站备份比", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "practicalequiptwo", ExcelColumn = "二级站实际配备数量", Alignment = "center" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
string fName = "基本防护装备配备标准_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
var num = wb.Worksheets[0].Cells.Columns.Count;
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[0, 0];
cell.PutValue("基本防护装备配备标准"); //标题
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 16;
cell.Style.Font.Color = Color.Black;
List <string> colList = new List <string>()
{
"名称", "主要用途", "一级站配备", "一级站备份比", "一级站实际配备数量", "二级站配备", "二级站备份比", "二级站实际配备数量"
};
List <string> colList1 = new List <string>()
{
"equipmentname", "purpose", "equipone", "equipratioone", "practicalequipone", "equiptwo", "equipratiotwo", "practicalequiptwo"
};
for (int i = 0; i < colList.Count; i++)
{
//序号列
Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
serialcell.PutValue(" ");
for (int j = 0; j < colList.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[1, j];
//sheet.Cells.SetColumnWidth(j, 40);
curcell.Style.Pattern = BackgroundType.Solid;
curcell.Style.Font.Size = 12;
curcell.Style.Font.Color = Color.Black;
curcell.PutValue(colList[j].ToString()); //列头
}
Aspose.Cells.Cells cells = sheet.Cells;
cells.Merge(0, 0, 1, colList.Count);
}
for (int i = 0; i < exportTable.Rows.Count; i++)
{
//内容填充
for (int j = 0; j < colList1.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j];
curcell.PutValue(exportTable.Rows[i][colList1[j]].ToString());
}
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
//wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功。"));
}
//Quang Huy 2014-02-19
/// <summary>
/// Merge cells
/// </summary>
/// <param name="cellNameStart">tên của ô bắt đầu merge</param>
/// <param name="rowNumber">số dòng muốn merge</param>
/// <param name="collumnNumber">số cột muốn merge</param>
public void Merge(string cellNameStart, int rowNumber, int collumnNumber)
{
Aspose.Cells.Cell cell = _worksheet.Cells[cellNameStart];
_worksheet.Cells.Merge(cell.Row, cell.Column, rowNumber, collumnNumber);
}
public ActionResult ExportExcel(string queryJson, int mode)
{
string fileName = string.Empty;
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.sidx = " nvl(punishdate,sysdate-10000) desc,nvl(createdate,sysdate -10000) desc,deptsort asc,sortcode asc,userid";
pagination.sord = " desc";
Operator curUser = ERCHTMS.Code.OperatorProvider.Provider.Current();
string userId = curUser.UserId;
try
{
//取出扣分数据源
DataTable exportTable = lllegaldeductmarksbll.GetLllegalRecordInfo(pagination, queryJson);
//取出人员积分数据源
pagination.sidx = " deptsort asc,sortcode asc,userid";
DataTable userTable = lllegaldeductmarksbll.GetLllegalPointInfo(pagination, queryJson);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
//生产部门
if (mode == 0)
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/生产部门违章档案(厂部)模板.xls"));
fileName = "生产部门违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
else
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/外协单位违章档案(厂部)模板.xls"));
fileName = "外协单位违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
string title = string.Empty;
string title0 = string.Empty;
//第一张表
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Cells cells = sheet.Cells;
//第二张表
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[1] as Aspose.Cells.Worksheet;
Cells cells1 = sheet1.Cells;
Aspose.Cells.Cell cell0 = sheet.Cells[0, 0];
Aspose.Cells.Cell cell00 = sheet1.Cells[0, 0];
if (mode == 0)
{
title = curUser.OrganizeName + "生产部门个人违章扣分表";
cell0.PutValue(title);
title0 = curUser.OrganizeName + "生产部门个人违章档案(厂级)";
cell00.PutValue(title0);
}
else
{
title = curUser.OrganizeName + "外协单位个人违章扣分表";
cell0.PutValue(title);
title0 = curUser.OrganizeName + "外协单位个人违章档案(厂级)";
cell00.PutValue(title0);
}
int rowIndex = 2;
//遍历行
#region 第一张表
foreach (DataRow row in exportTable.Rows)
{
if (rowIndex - 2 < exportTable.Rows.Count - 1)
{
cells.CopyRow(cells, rowIndex, rowIndex + 1);
}
string username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
Aspose.Cells.Cell rcell1 = sheet.Cells[rowIndex, 1];
rcell1.PutValue(username);
string deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
Aspose.Cells.Cell rcell2 = sheet.Cells[rowIndex, 2];
rcell2.PutValue(deptname);
string teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
Aspose.Cells.Cell rcell3 = sheet.Cells[rowIndex, 3];
rcell3.PutValue(teamname);
string dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
Aspose.Cells.Cell rcell4 = sheet.Cells[rowIndex, 4];
rcell4.PutValue(dutyname);
string lllegaldescribe = !string.IsNullOrEmpty(row["lllegaldescribe"].ToString()) ? row["lllegaldescribe"].ToString() : "";
Aspose.Cells.Cell rcell5 = sheet.Cells[rowIndex, 5];
rcell5.PutValue(lllegaldescribe);
string lllegaltypename = !string.IsNullOrEmpty(row["lllegaltypename"].ToString()) ? row["lllegaltypename"].ToString() : "";
Aspose.Cells.Cell rcell6 = sheet.Cells[rowIndex, 6];
rcell6.PutValue(lllegaltypename);
string punishdate = !string.IsNullOrEmpty(row["punishdate"].ToString()) ? Convert.ToDateTime(row["punishdate"].ToString()).ToString("yyyy-MM-dd") : "";
Aspose.Cells.Cell rcell7 = sheet.Cells[rowIndex, 7];
rcell7.PutValue(punishdate);
string punishresult = !string.IsNullOrEmpty(row["punishresult"].ToString()) ? row["punishresult"].ToString() : "0";
Aspose.Cells.Cell rcell8 = sheet.Cells[rowIndex, 8];
rcell8.PutValue(row["punishresult"].ToString());
string punishpoint = !string.IsNullOrEmpty(row["punishpoint"].ToString()) ? row["punishpoint"].ToString() : "0";
Aspose.Cells.Cell rcell9 = sheet.Cells[rowIndex, 9];
rcell9.PutValue(punishpoint);
rcell9.R1C1Formula = "=I" + (rowIndex + 1).ToString() + "/100";
rowIndex += 1;
}
#endregion
rowIndex = 2;
#region 第二张表
foreach (DataRow row in userTable.Rows)
{
if (rowIndex - 2 < userTable.Rows.Count - 1)
{
cells1.CopyRow(cells1, rowIndex, rowIndex + 1);
}
string username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
Aspose.Cells.Cell rcell1 = sheet1.Cells[rowIndex, 1];
rcell1.PutValue(username);
string deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
Aspose.Cells.Cell rcell2 = sheet1.Cells[rowIndex, 2];
rcell2.PutValue(deptname);
string teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
Aspose.Cells.Cell rcell3 = sheet1.Cells[rowIndex, 3];
rcell3.PutValue(teamname);
string dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
Aspose.Cells.Cell rcell4 = sheet1.Cells[rowIndex, 4];
rcell4.PutValue(dutyname);
string initpoint = !string.IsNullOrEmpty(row["initpoint"].ToString()) ? row["initpoint"].ToString() : "100";
Aspose.Cells.Cell rcell5 = sheet1.Cells[rowIndex, 5];
rcell5.PutValue(initpoint);
Aspose.Cells.Cell rcell6 = sheet1.Cells[rowIndex, 6];
if (mode == 0)
{
rcell6.R1C1Formula = "=SUMIF(生产部门违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",生产部门违章扣分表!$J$3:$J$10000)";
}
else
{
rcell6.R1C1Formula = "=SUMIF(外协单位违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",外协单位违章扣分表!$J$3:$J$10000)";
}
Aspose.Cells.Cell rcell7 = sheet1.Cells[rowIndex, 7];
rcell7.R1C1Formula = "=F" + (rowIndex + 1).ToString() + "-G" + (rowIndex + 1).ToString();
rowIndex += 1;
}
#endregion
string tempSavePath = Server.MapPath("~/Resource/Temp/") + fileName;
wb.Save(tempSavePath);
string url = "../../Utility/DownloadFile?filePath=~/Resource/Temp/" + fileName + "&speed=10240000&newFileName=" + fileName;
return(Redirect(url));
}
catch (Exception ex)
{
return(Error(ex.Message));
}
}
private Workbook AggregateDocuments(SortedDictionary <string, List <Document> > DMDocs)
{
const int VALUE_COLUMN_WIDTH = 12;
Aspose.Cells.Workbook AggregateBook = null;
const string AggregateFilePrefix = "Aggregate_";
string ErrorMessage = string.Empty;
List <byte[]> FilesToAggregate = new List <byte[]>();
byte[] AggregateFile; bool AggregateTemplateFound = false;
#region Get the document containing the aggregate filename
foreach (string dm in DMDocs.Keys)
{
foreach (IDnsPersistentDocument doc in DMDocs[dm])
{
Stream contentStream = doc.ReadStream();
//doc is a zipped file.
Dictionary <string, byte[]> ExtractedFiles = ExtractZipFile(contentStream, doc.Name);
if (ExtractedFiles.Count() <= 0)
{
ExtractedFiles.Add(doc.Name, GetBytesFromStream(contentStream, doc.BodySize));
}
foreach (string key in ExtractedFiles.Keys)
{
if (key.StartsWith(AggregateFilePrefix))
{
if (!AggregateTemplateFound)
{
AggregateFile = ExtractedFiles[key];
AggregateTemplateFound = true;
//Load the aggregate file.
MemoryStream ms = new MemoryStream();
ms.Write(AggregateFile, 0, AggregateFile.Length);
ms.Position = 0;
try
{
AggregateBook = new Aspose.Cells.Workbook(ms);
}
catch (Exception) { AggregateBook = null; }
ms.Close(); AggregateFile = null;
if (AggregateBook != null)
{
//SAS exported Aggregate template contains formula fields but suppressed by apostrophs(') in the beginning.
//Remove apostrophs from the aggregate template so that suppressed formulae are restored.
foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets)
{
foreach (Aspose.Cells.Cell cl in ws.Cells)
{
if (!cl.IsFormula &&
!string.IsNullOrEmpty(cl.StringValue) &&
cl.StringValue.StartsWith("="))
{
cl.Formula = cl.StringValue;
}
}
}
AggregateTemplateFound = true;
}
}
}
else
{
FilesToAggregate.Add(ExtractedFiles[key]);
}
}
}
}
#endregion
if (AggregateTemplateFound)
{
//Iterate through each datamart. Aggregate the files from each datamart into the aggregate book.
foreach (byte[] bytes in FilesToAggregate)
{
//SKIP the Aggregate Template File which are uploaded by each DataMart.
//Create a Source Workbook from Byte Arrary obtained from Database.
MemoryStream ms = new MemoryStream();
ms.Write(bytes, 0, bytes.Length);
ms.Position = 0;
//If Memorystream cannot be read as Aspose Cells, then ignore and move on to next memory stream.
Aspose.Cells.Workbook FileToAggregate = null;
try
{
FileToAggregate = new Aspose.Cells.Workbook(ms);
}
catch (Exception) { FileToAggregate = null; }
ms.Close();
if (FileToAggregate != null)
{
//Copy the worksheets from Source Book (ws) to the corresponding worksheet in the Aggregate Book.
foreach (Aspose.Cells.Worksheet ws in FileToAggregate.Worksheets)
{
Aspose.Cells.Worksheet targetsheet = AggregateBook.Worksheets[ws.Name];
if (targetsheet != null)
{
foreach (Aspose.Cells.Cell wcell in ws.Cells)
{
Aspose.Cells.Cell tcell = targetsheet.Cells[wcell.Row, wcell.Column];
if (tcell != null)
{
tcell.Copy(wcell);
}
}
}
}
}
}
/* Set the formula field width = 12 characters */
foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets)
{
foreach (Aspose.Cells.Column col in ws.Cells.Columns)
{
foreach (Aspose.Cells.Cell fld in ws.Cells)
{
if (col.Index == fld.Column && (fld.IsFormula || fld.Type == Aspose.Cells.CellValueType.IsNumeric))
{
col.Width = VALUE_COLUMN_WIDTH;
break;
}
}
}
}
}
else
{
ErrorMessage = "Cannot locate the Aggregate Template File.";
throw (new Exception(ErrorMessage));
}
return(AggregateBook);
}
public ActionResult ExportExamineData(string queryJson)
{
try
{
Operator user = OperatorProvider.Provider.Current();
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 1000000000;
var watch = CommonHelper.TimerStart();
pagination.p_kid = "t.id";
var table = @"(select
examinetodeptid,wm_concat(id) id,
examinetodept,max(createdate) createdate,
wm_concat(distinct(examineperson)) examineperson,
to_char(min(examinetime),'yyyy-MM-dd')||'~'|| to_char(max(examinetime),'yyyy-MM-dd') examinetime,
sum(examinemoney) examinemoney,
wm_concat(examinetype) examinetype,createuserorgcode
from epg_dailyexamine t where 1=1 {0} group by examinetodeptid,examinetodept,createuserorgcode) t";
var strWhere = string.Empty;
pagination.p_fields = @" t.examinetodeptid,
t.examinetodept,
t.examineperson,
t.examinetime,
t.examinemoney,
t.examinetype";
pagination.conditionJson = "1=1";
pagination.sidx = "t.createdate";
pagination.sord = "desc";
if (!user.IsSystem)
{
pagination.conditionJson += " and t.createuserorgcode='" + user.OrganizeCode + "'";
}
if (!string.IsNullOrEmpty(queryJson))
{
var queryParam = queryJson.ToJObject();
if (!queryParam["examinetodeptid"].IsEmpty())
{
strWhere += " and t.examinetodeptid ='" + queryParam["examinetodeptid"].ToString() + "'";
}
if (!queryParam["examinetype"].IsEmpty())
{
strWhere += " and t.examinetype='" + queryParam["examinetype"].ToString() + "'";
}
if (!queryParam["examinecontent"].IsEmpty())
{
strWhere += " and t.examinecontent like '%" + queryParam["examinecontent"].ToString() + "%'";
}
//开始时间
if (!queryParam["sTime"].IsEmpty())
{
strWhere += string.Format(@" and t.examinetime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", queryParam["sTime"].ToString());
}
//结束时间
if (!queryParam["eTime"].IsEmpty())
{
strWhere += string.Format(@" and t.examinetime < to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Convert.ToDateTime(queryParam["eTime"].ToString()).AddDays(1).ToString("yyyy-MM-dd"));
}
}
table = string.Format(table, strWhere);
pagination.p_tablename = table;
var data = dailyexaminebll.GetExportExamineCollent(pagination, queryJson);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
string fName = "日常考核汇总_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
var num = wb.Worksheets[0].Cells.Columns.Count;
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[0, 0];
cell.PutValue("考核汇总表"); //标题
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 16;
cell.Style.Font.Color = Color.Black;
List <string> colList = new List <string>()
{
"被考核单位", "考核金额", "考核类型", "考核人", "考核时间"
};
List <string> colList1 = new List <string>()
{
"examinetodept", "examinemoney", "examinetype", "examineperson", "examinetime"
};
for (int i = 0; i < colList.Count; i++)
{
//序号列
Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
serialcell.PutValue(" ");
for (int j = 0; j < colList.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[1, j + 1];
sheet.Cells.SetColumnWidth(j + 1, 40);
curcell.Style.Pattern = BackgroundType.Solid;
curcell.Style.Font.Size = 12;
curcell.Style.Font.Color = Color.Black;
curcell.PutValue(colList[j].ToString()); //列头
}
Aspose.Cells.Cells cells = sheet.Cells;
cells.Merge(0, 0, 1, colList.Count + 1);
}
for (int i = 0; i < data.Rows.Count; i++)
{
//序列号
Aspose.Cells.Cell serialcell = sheet.Cells[i + 2, 0];
if (string.IsNullOrWhiteSpace(data.Rows[i]["parent"].ToString()))
{
serialcell.PutValue("合计");
}
//内容填充
for (int j = 0; j < colList1.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j + 1];
curcell.PutValue(data.Rows[i][colList1[j]].ToString());
}
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
return(Success("导出成功。", fName));
}
catch (Exception)
{
throw;
}
}
public static void XuatDuLieuRaExcel(int iRowPara,
int iColumnPara,
string strSubHeaderPara,
System.Data.DataTable tblBangDuLieuPara,
string strTemplateNamePara)
{
//Đường dẫn file template
string strSourceFilePri = string.Format("{0}{1}{2}",
System.Windows.Forms.Application.StartupPath,
PATH_TEMPLATES, strTemplateNamePara);
SaveFileDialog saveFileDialogPri = new SaveFileDialog();
saveFileDialogPri.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialogPri.FilterIndex = 1;
if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
{
FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(streamTemp);
workbook.Worksheets.Add();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
//Set cell store subHeader
Aspose.Cells.Cells cellHeader = worksheet.Cells;
//cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
worksheet.Cells["A3"].PutValue(strSubHeaderPara);
//worksheet.IsGridlinesVisible = false;
worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);
//Formatting for cells store database
for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
{
for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
{
Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
workbook.Styles.Add();
Aspose.Cells.Style style = cell.GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Silver;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Silver;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Silver;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Silver;
cell.SetStyle(style);
}
}
//worksheet.AutoFitColumns();
//Save excel file
workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);
MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
WorkingContext.LangManager.GetString("Message"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
streamTemp.Close();
if (File.Exists(saveFileDialogPri.FileName))
{
Process.Start(saveFileDialogPri.FileName);
}
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Create a workbook.
Workbook workbook = new Workbook();
//Obtaining the reference of the default(first) worksheet
Worksheet sheet = workbook.Worksheets[0];
//Obtaining Worksheet's cells collection
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");
cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James");
cell = cells["A7"];
cell.PutValue("James");
cell = cells["A8"];
cell.PutValue("James");
cell = cells["A9"];
cell.PutValue("James");
cell = cells["A10"];
cell.PutValue("James");
cell = cells["A11"];
cell.PutValue("Miya");
cell = cells["A12"];
cell.PutValue("Miya");
cell = cells["A13"];
cell.PutValue("Miya");
cell = cells["A14"];
cell.PutValue("Miya");
cell = cells["A15"];
cell.PutValue("Miya");
cell = cells["B2"];
cell.PutValue(1);
cell = cells["B3"];
cell.PutValue(2);
cell = cells["B4"];
cell.PutValue(3);
cell = cells["B5"];
cell.PutValue(4);
cell = cells["B6"];
cell.PutValue(1);
cell = cells["B7"];
cell.PutValue(2);
cell = cells["B8"];
cell.PutValue(3);
cell = cells["B9"];
cell.PutValue(4);
cell = cells["B10"];
cell.PutValue(4);
cell = cells["B11"];
cell.PutValue(1);
cell = cells["B12"];
cell.PutValue(1);
cell = cells["B13"];
cell.PutValue(2);
cell = cells["B14"];
cell.PutValue(2);
cell = cells["B15"];
cell.PutValue(2);
cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang");
cell = cells["C7"];
cell.PutValue("Chang");
cell = cells["C8"];
cell.PutValue("Chang");
cell = cells["C9"];
cell.PutValue("Chang");
cell = cells["C10"];
cell.PutValue("Chang");
cell = cells["C11"];
cell.PutValue("Geitost");
cell = cells["C12"];
cell.PutValue("Chai");
cell = cells["C13"];
cell.PutValue("Geitost");
cell = cells["C14"];
cell.PutValue("Geitost");
cell = cells["C15"];
cell.PutValue("Geitost");
cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe");
cell = cells["D7"];
cell.PutValue("Europe");
cell = cells["D8"];
cell.PutValue("Europe");
cell = cells["D9"];
cell.PutValue("Europe");
cell = cells["D10"];
cell.PutValue("Europe");
cell = cells["D11"];
cell.PutValue("America");
cell = cells["D12"];
cell.PutValue("America");
cell = cells["D13"];
cell.PutValue("America");
cell = cells["D14"];
cell.PutValue("America");
cell = cells["D15"];
cell.PutValue("America");
cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France");
cell = cells["E7"];
cell.PutValue("France");
cell = cells["E8"];
cell.PutValue("Germany");
cell = cells["E9"];
cell.PutValue("Italy");
cell = cells["E10"];
cell.PutValue("France");
cell = cells["E11"];
cell.PutValue("U.S.");
cell = cells["E12"];
cell.PutValue("U.S.");
cell = cells["E13"];
cell.PutValue("Brazil");
cell = cells["E14"];
cell.PutValue("U.S.");
cell = cells["E15"];
cell.PutValue("U.S.");
cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500);
cell = cells["F7"];
cell.PutValue(1500);
cell = cells["F8"];
cell.PutValue(800);
cell = cells["F9"];
cell.PutValue(900);
cell = cells["F10"];
cell.PutValue(500);
cell = cells["F11"];
cell.PutValue(1600);
cell = cells["F12"];
cell.PutValue(600);
cell = cells["F13"];
cell.PutValue(2000);
cell = cells["F14"];
cell.PutValue(500);
cell = cells["F15"];
cell.PutValue(900);
//Adding a new List Object to the worksheet
Aspose.Cells.Tables.ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add("A1", "F15", true)];
//Adding Default Style to the table
listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium10;
//Show Total
listObject.ShowTotals = true;
//Set the Quarter field's calculation type
listObject.ListColumns[1].TotalsCalculation = Aspose.Cells.Tables.TotalsCalculation.Count;
//Saving the Excel file
workbook.Save(dataDir + "List_Object.xlsx");
}
public ActionResult Export(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "rownum idx";
pagination.p_fields = @"t.Name,(Concat(t.Specification,t.SpecificationUnit)) as Specification,
t.risktype,t.site,
(Concat(t.receivenum,t.receiveUnit)) as receivenum,t.receiveuser,t.grantuser,t.purpose";
pagination.p_tablename = "XLD_DANGEROUSCHEMICALRECEIVE t left join XLD_DANGEROUSCHEMICAL t1 on t.mainid=t1.id";
//pagination.sidx = "createdate";//排序字段
//pagination.sord = "desc";//排序方式
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
pagination.conditionJson = "1=1";
}
else
{
pagination.conditionJson = string.Format(" CREATEUSERORGCODE ='{0}'", user.OrganizeCode);
}
var watch = CommonHelper.TimerStart();
var data = DangerChemicalsReceiveBll.GetList(pagination, queryJson);
HttpResponse resp = System.Web.HttpContext.Current.Response;
// 详细列表内容
string fielname = "危化品领用记录" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[0, 0];
cell.PutValue("危化品领用记录"); //标题
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 16;
cell.Style.Font.Color = Color.Black;
//Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;//文字居中
//style.IsTextWrapped = true;//自动换行
//style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
//style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
//style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
//style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
Aspose.Cells.Cells cells = sheet.Cells;
cells.Merge(0, 0, 1, 9);
sheet.Cells[1, 0].PutValue("序号");
sheet.Cells[1, 1].PutValue("名称");
sheet.Cells[1, 2].PutValue("规格");
sheet.Cells[1, 3].PutValue("危险品类型");
sheet.Cells[1, 4].PutValue("存放地点");
sheet.Cells[1, 5].PutValue("领用数量");
sheet.Cells[1, 6].PutValue("领用人");
sheet.Cells[1, 7].PutValue("发放人");
sheet.Cells[1, 8].PutValue("用途及使用说明");
sheet.Cells.SetColumnWidthPixel(8, 250);
int rowIndex = 2;
foreach (DataRow row in data.Rows)
{
Aspose.Cells.Cell idxcell = sheet.Cells[rowIndex, 0];
idxcell.PutValue(row["idx"]);
Aspose.Cells.Cell namexcell = sheet.Cells[rowIndex, 1];
namexcell.PutValue(row["name"]);
Aspose.Cells.Cell specificationcell = sheet.Cells[rowIndex, 2];
specificationcell.PutValue(row["specification"]);
Aspose.Cells.Cell risktypexcell = sheet.Cells[rowIndex, 3];
risktypexcell.PutValue(row["risktype"]);
Aspose.Cells.Cell sitexcell = sheet.Cells[rowIndex, 4];
sitexcell.PutValue(row["site"]);
Aspose.Cells.Cell receivenumxcell = sheet.Cells[rowIndex, 5];
receivenumxcell.PutValue(row["receivenum"]);
Aspose.Cells.Cell receiveuserxcell = sheet.Cells[rowIndex, 6];
receiveuserxcell.PutValue(row["receiveuser"]);
Aspose.Cells.Cell grantuserxcell = sheet.Cells[rowIndex, 7];
grantuserxcell.PutValue(row["grantuser"]);
Aspose.Cells.Cell purposecell = sheet.Cells[rowIndex, 8];
purposecell.PutValue(row["purpose"]);
rowIndex++;
}
//设置导出格式
//ExcelConfig excelconfig = new ExcelConfig();
//excelconfig.Title = "危化品领用记录";
//excelconfig.TitleFont = "微软雅黑";
//excelconfig.TitlePoint = 16;
//excelconfig.FileName = "危化品领用记录.xls";
//excelconfig.IsAllSizeColumn = true;
////每一列的设置,没有设置的列信息，系统将按datatable中的列名导出
//List<ColumnEntity> listColumnEntity = new List<ColumnEntity>();
//excelconfig.ColumnEntity = listColumnEntity;
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "idx", ExcelColumn = "序号", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "name", ExcelColumn = "名称", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "specification", ExcelColumn = "规格", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "risktype", ExcelColumn = "危险品类型", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "site", ExcelColumn = "存放地点", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "receivenum", ExcelColumn = "领用数量", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "receiveuser", ExcelColumn = "领用人", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "grantuser", ExcelColumn = "发放人", Alignment = "center" });
//excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "purpose", ExcelColumn = "用途及使用说明", Alignment = "center" });
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功。"));
}
public ActionResult Export(string queryJson)
{
try
{
var exportTable = SafetyMeshbll.GetTableList(queryJson);
////调用导出方法
//ExcelHelper.ExcelDownload(data, excelconfig);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
string fName = "安全网格_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
var num = wb.Worksheets[0].Cells.Columns.Count;
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[0, 0];
cell.PutValue("安全网格"); //标题
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 16;
cell.Style.Font.Color = Color.Black;
List <string> colList = new List <string>()
{
"网格名称", "上级网格名称", "网络责任人", "联系电话", "关联区域", "网络级别", "工作职责"
};
List <string> colList1 = new List <string>()
{
"meshname", "superiorname", "dutyuser", "dutytel", "district", "meshrank", "workjob"
};
for (int i = 0; i < colList.Count; i++)
{
//序号列
Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
serialcell.PutValue(" ");
for (int j = 0; j < colList.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[1, j];
//sheet.Cells.SetColumnWidth(j, 40);
curcell.Style.Pattern = BackgroundType.Solid;
curcell.Style.Font.Size = 12;
curcell.Style.Font.Color = Color.Black;
curcell.PutValue(colList[j].ToString()); //列头
}
Aspose.Cells.Cells cells = sheet.Cells;
cells.Merge(0, 0, 1, colList.Count);
}
for (int i = 0; i < exportTable.Rows.Count; i++)
{
//内容填充
for (int j = 0; j < colList1.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j];
curcell.PutValue(exportTable.Rows[i][colList1[j]].ToString());
}
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
//wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功。"));
}
catch (Exception ex)
{
return(Error(ex.Message));
}
}
//Quang Huy 2014-02-19
/// <summary>
/// Thực hiện đặt viền cho ô
/// </summary>
/// <param name="cellName">Tên của ô (VD "B6")</param>
/// <param name="position">Vị trí viền (mặc định viền xung quanh)</param>
/// <param name="color">màu viền (mặc định đen)</param>
public bool SetCellBorder(string cellName, BorderPosition position = BorderPosition.Around, System.Drawing.Color?color = null)
{
Aspose.Cells.Cell cell = _worksheet.Cells[cellName];
return(SetCellBorder(cell, position, color));
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiate a new workbook
Workbook workbook = new Workbook();
// Get the cells in the first(default) worksheet
Cells cells = workbook.Worksheets[0].Cells;
// Get the A1 cell
Aspose.Cells.Cell p = cells["A1"];
// Enter a value
p.PutValue("Preface");
// Get the A10 cell
Aspose.Cells.Cell A = cells["A10"];
// Enter a value.
A.PutValue("page1");
// Get the H15 cell
Aspose.Cells.Cell D = cells["H15"];
// Enter a value
D.PutValue("page1(H15)");
// Add a new worksheet to the workbook
workbook.Worksheets.Add();
// Get the cells in the second sheet
cells = workbook.Worksheets[1].Cells;
// Get the B10 cell in the second sheet
Aspose.Cells.Cell B = cells["B10"];
// Enter a value
B.PutValue("page2");
// Add a new worksheet to the workbook
workbook.Worksheets.Add();
// Get the cells in the third sheet
cells = workbook.Worksheets[2].Cells;
// Get the C10 cell in the third sheet
Aspose.Cells.Cell C = cells["C10"];
// Enter a value
C.PutValue("page3");
// Create a main PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry pbeRoot = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
pbeRoot.Text = "Sections";
// Set the destination cell/location
pbeRoot.Destination = p;
// Set its sub entry array list
pbeRoot.SubEntry = new ArrayList();
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe1 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe1.Text = "Section 1";
// Set its destination cell
subPbe1.Destination = A;
// Define/Create a sub Bookmark entry object of "Section A"
Aspose.Cells.Rendering.PdfBookmarkEntry ssubPbe = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
ssubPbe.Text = "Section 1.1";
// Set its destination
ssubPbe.Destination = D;
// Create/Set its sub entry array list object
subPbe1.SubEntry = new ArrayList();
// Add the object to "Section 1"
subPbe1.SubEntry.Add(ssubPbe);
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe1);
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe2 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe2.Text = "Section 2";
// Set its destination
subPbe2.Destination = B;
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe2);
// Create a sub PDF Bookmark entry object
Aspose.Cells.Rendering.PdfBookmarkEntry subPbe3 = new Aspose.Cells.Rendering.PdfBookmarkEntry();
// Specify its text
subPbe3.Text = "Section 3";
// Set its destination
subPbe3.Destination = C;
// Add the object to the main PDF root object
pbeRoot.SubEntry.Add(subPbe3);
// Create an instance of PdfSaveOptions
Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
// Set the PDF Bookmark root object
pdfSaveOptions.Bookmark = pbeRoot;
// Save the pdf file
workbook.Save(outputDir + "outputAddPDFBookmarks.pdf", pdfSaveOptions);
Console.WriteLine("AddPDFBookmarks executed successfully.\r\n");
}
