private XlsSaveOptions GetSaveFormat(string format)
{
XlsSaveOptions saveOption = new XlsSaveOptions(SaveFormat.Xlsx);
switch (format.ToLower())
{
case "xlsx":
saveOption = new XlsSaveOptions(SaveFormat.Xlsx); break;
case "xlsb":
saveOption = new XlsSaveOptions(SaveFormat.Xlsb); break;
case "xls":
saveOption = new XlsSaveOptions(SaveFormat.Excel97To2003); break;
case "txt":
saveOption = new XlsSaveOptions(SaveFormat.TabDelimited); break;
case "csv":
saveOption = new XlsSaveOptions(SaveFormat.CSV); break;
case "ods":
saveOption = new XlsSaveOptions(SaveFormat.ODS); break;
default:
saveOption = new XlsSaveOptions(SaveFormat.Excel97To2003); break;
}
return(saveOption);
}
protected void Button1_Click(object sender, EventArgs e)
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(Server.MapPath("./") + "Template\\mb.xls");
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells[4, 1].PutValue("50H工具箱");
XlsSaveOptions saveOptions = new XlsSaveOptions();
workbook.Save(Response, "result.xls", ContentDisposition.Inline, saveOptions);
}
public static void SaveExportCustom(Workbook wb, string report_name)
{
try
{
SaveOptions saveOptions = new XlsSaveOptions(SaveFormat.Xlsx);
wb.Worksheets.RemoveAt(wb.Worksheets.Count - 1);
wb.Save(Temp + @"\" + report_name + "abc.xlsx", saveOptions);
}
catch (Exception ex)
{
Logger.Error(ex);
return;
}
}
private XlsSaveOptions GetSaveFormat(string format)
{
XlsSaveOptions saveOption = new XlsSaveOptions(SaveFormat.Xlsx);
switch (format)
{
case "xlsx":
saveOption = new XlsSaveOptions(SaveFormat.Xlsx); break;
case "xlsb":
saveOption = new XlsSaveOptions(SaveFormat.Xlsb); break;
case "xls":
saveOption = new XlsSaveOptions(SaveFormat.Excel97To2003); break;
case "txt":
saveOption = new XlsSaveOptions(SaveFormat.TabDelimited); break;
case "csv":
saveOption = new XlsSaveOptions(SaveFormat.CSV); break;
case "ods":
saveOption = new XlsSaveOptions(SaveFormat.ODS); break;
}
return saveOption;
}
public static void SaveExportCustom(Workbook wb, string report_name)
{
try
{
SaveOptions saveOptions = new XlsSaveOptions(SaveFormat.Xlsx);
wb.Worksheets.RemoveAt(wb.Worksheets.Count - 1);
wb.Save(Temp + @"\" + report_name + "abc.xlsx", saveOptions);
}
catch (Exception ex)
{
Logger.Error(ex);
return;
}
}
private int ExportDataBulk(Func <int, int, ICollection> getDataFunc)
{
int totalCount = 0;
int bulkCount = 20000;
string templateFile = System.Web.HttpContext.Current.Server.MapPath(@"~\Excel\Template\ExcelTemplate.xlsx");
string exportFileName = DateTime.Now.Ticks.ToString();
exportFileName = System.Web.HttpContext.Current.Server.MapPath(@"~\Excel\Export\" + exportFileName + ".xlsx");
object[,] templateValueArray = null;
using (FileStream fileStream = new FileStream(templateFile, FileMode.Open))
{
WorkbookDesigner designer = new WorkbookDesigner(new Workbook(fileStream));
Cells cells = designer.Workbook.Worksheets[0].Cells;
templateValueArray = cells.ExportArray(1, 0, 1, cells.Columns.Count);
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
while (totalCount <= 1000000)
{
ICollection bulkData = getDataFunc(totalCount, bulkCount);
if (bulkData == null || bulkData.Count <= 0)
{
break;
}
string loadFileName = (totalCount <= 0 ? templateFile : exportFileName);
using (FileStream fileStream = new FileStream(loadFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
using (Workbook workbook = new Workbook(fileStream))
{
WorkbookDesigner designer = new WorkbookDesigner(workbook);
using (Cells cells = workbook.Worksheets[0].Cells)
{
if (totalCount > 0)
{
cells.ImportTwoDimensionArray(templateValueArray, totalCount + 1, 0);
}
designer.SetDataSource("Product", bulkData);
designer.Process();
designer.ClearDataSource();
XlsSaveOptions xlsSaveOptions = new XlsSaveOptions()
{
ClearData = true
};
workbook.Save(exportFileName, xlsSaveOptions);
cells.Dispose();
}
workbook.Dispose();
}
fileStream.Close();
fileStream.Dispose();
}
totalCount += bulkData.Count;
if (bulkData.Count < bulkCount)
{
break;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return(totalCount);
}
public void CreateReport(String HitTaskID)
{
int i, j, k, iCount = 1, t;
String Type, Note = "", mTableName, subTableName, zdName, State;/*批注*/
DataRow[] dr, dr1;
DataTable dt2;
dt2 = MyManager.GetDataSet("SELECT * From [HitTool] Where State = 1 AND TaskID = '" + HitTaskID + "'");
if (dt2.Rows.Count == 0)
{
return;
}
Type = dt2.Rows[0]["Type"].ToString();
if (Type == "1")
{
mTableName = "StoredTool"; subTableName = "StoredToolValue"; zdName = "StoredID"; State = "4";
}
else
{
mTableName = "CoreTool"; subTableName = "CoreToolValue"; zdName = "CoreID"; State = "1";
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(Server.MapPath("./") + "Template\\mb.xls");
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
DataTable dt = MyManager.GetDataSet("SELECT * FROM " + mTableName + " where [State] = " + State + " AND [RelatedTask] = '" + HitTaskID + "'");
for (i = 0, iCount = 1; i < dt.Rows.Count; i++)
{
DataTable dt1 = MyManager.GetDataSet("SELECT A.*,B.Name FROM " + subTableName + " AS A join [CLassPropertys] AS B on A.PropertyID = B.ID Where " + zdName + " = '" + dt.Rows[i]["ID"].ToString() + "'");
sheet = workbook.Worksheets[workbook.Worksheets.AddCopy("Bak")];
cells = sheet.Cells;
dr = dt1.Select("ValueType = 3 ");
for (j = 0; j < dr.Length; j++)
{
t = j % 44; //t始终在0-51内变化
if (t == 0 && j != 0) /*证明本页已经填完，需要加页*/
{
sheet = workbook.Worksheets[workbook.Worksheets.AddCopy("Bak")];
cells = sheet.Cells;
}
if (j % 22 == 0)/*页内或者页间表格切换*/
{
cells[2, (t / 22) * 6].PutValue(" 工具箱编号:" + dt.Rows[i][Type == "1" ? "rkID" : "ToolID"].ToString() + " 名称:" + dt.Rows[i][Type == "1" ? "StoredName" : "ToolName"].ToString());
//------------------增加批注-----------------------
dr1 = dt1.Select("ValueType = 2 AND ParentID = " + dt.Rows[i]["ID"].ToString());
for (k = 0, Note = ""; k < dr1.Length; k++)
{
Note += dr1[k]["Name"].ToString().Trim() + ":" + dr1[k]["Value"].ToString() + "\n\r";
}
sheet.Comments.Add(2, (t / 22) * 6);
sheet.Comments[2, (t / 22) * 6].Note = Note;
//------------------------------------------------
}
cells[4 + t % 22, (t / 22) * 6].PutValue(j + 1);
cells[4 + t % 22, (t / 22) * 6 + 1].PutValue(dr[j]["Value"].ToString());
cells[4 + t % 22, (t / 22) * 6 + 2].PutValue(dr[j][Type == "1" ? "rkID" : "ToolID"].ToString());
//------------------增加批注-----------------------
dr1 = dt1.Select("ValueType = 4 AND ParentID = " + dr[j]["ID"].ToString());
for (k = 0, Note = ""; k < dr1.Length; k++)
{
Note += dr1[k]["Name"].ToString().Trim() + ":" + dr1[k]["Value"].ToString() + "\n\r";
}
sheet.Comments.Add(4 + t % 22, (t / 22) * 6 + 1);
sheet.Comments[4 + t % 22, (t / 22) * 6 + 1].Note = Note;
//------------------------------------------------
iCount++;
}
}
workbook.Worksheets.RemoveAt("Bak");
XlsSaveOptions saveOptions = new XlsSaveOptions();
workbook.Save(Response, "工具清单.xls", ContentDisposition.Inline, saveOptions);
}