public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
// Create an instance of Workbook and load an existing spreadsheet
var book = new Workbook(sourceDir + "sampleDetectEmptyWorksheets.xlsx");
// Loop over all worksheets in the workbook
for (int i = 0; i < book.Worksheets.Count; i++)
{
Worksheet sheet = book.Worksheets[i];
// Check if worksheet has populated cells
if (sheet.Cells.MaxDataRow != -1)
{
Console.WriteLine(sheet.Name + " is not Empty because one or more Cells are Populated");
}
// Check if worksheet has shapes
else if (sheet.Shapes.Count > 0)
{
Console.WriteLine(sheet.Name + " is not Empty because there are one or more Shapes");
}
// Check if worksheet has empty initialized cells
else
{
Aspose.Cells.Range range = sheet.Cells.MaxDisplayRange;
var rangeIterator = range.GetEnumerator();
if (rangeIterator.MoveNext())
{
Console.WriteLine(sheet.Name + " is not Empty because one or more cells are Initialized");
}
}
}
Console.WriteLine("DetectEmptyWorksheets executed successfully.");
}
public static void Run()
{
// ExStart:DetectEmptyWorksheets
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an instance of Workbook and load an existing spreadsheet
var book = new Workbook(dataDir + "sample.xlsx");
// Loop over all worksheets in the workbook
for (int i = 0; i < book.Worksheets.Count; i++)
{
Worksheet sheet = book.Worksheets[i];
// Check if worksheet has populated cells
if (sheet.Cells.MaxDataRow != -1)
{
Console.WriteLine(sheet.Name + " is not empty because one or more cells are populated");
}
// Check if worksheet has shapes
else if (sheet.Shapes.Count > 0)
{
Console.WriteLine(sheet.Name + " is not empty because there are one or more shapes");
}
// Check if worksheet has empty initialized cells
else
{
Aspose.Cells.Range range = sheet.Cells.MaxDisplayRange;
var rangeIterator = range.GetEnumerator();
if (rangeIterator.MoveNext())
{
Console.WriteLine(sheet.Name + " is not empty because one or more cells are initialized");
}
}
}
// ExEnd:DetectEmptyWorksheets
}
public ActionResult ExportQD(string keyValue)
{
//获取会议基本信息
ConferenceEntity cr = conferencebll.GetEntity(keyValue);
////获取会议签到人员信息
//List<Object> data = conferenceuserbll.GetSignTable(keyValue);
////签到
//List<Object> dataQD = data[0] as List<Object>;
////未签到
//List<Object> dataWQD = data[1] as List<Object>;
////请假
//List<Object> dataQ = data[2] as List<Object>;
string path = Request.PhysicalApplicationPath;
int index = 0;
string fileName = HttpUtility.UrlDecode("安全会议签到") + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //定义导出文件名，一定记得编码
try
{
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
wk.Open(Server.MapPath("~/Resource/ExcelTemplate/安全会议签到表模板.xlsx")); //加载模板
Aspose.Cells.Worksheet sheet = wk.Worksheets[0];
Aspose.Cells.Style style = wk.Styles[wk.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;
string sql = string.Format(" select t.USERNAME,o.DEPTNAME,t.PHOTOURL,t.issign,t.REVIEWSTATE from BIS_ConferenceUser t left join v_userinfo o on t.userid=o.userid where t.ConferenceID='{0}'", keyValue);
DataTable dt = specialequipmentbll.SelectData(sql);
//参会人员集合
var listC = dt;
//签到人员集合
var SdUser = listC.Select(" issign='0'");
//未签到人员集合
var YdUser = listC.Select(" issign<>'0' and REVIEWSTATE<>'2'");
//请假人数
var QUser = listC.Select(" REVIEWSTATE='2'");
sheet.Cells[1, 2].PutValue(cr.ConferenceName); //会议名称
sheet.Cells[1, 4].PutValue(cr.Locale); //会议地点
sheet.Cells[2, 2].PutValue(cr.CompereDept); //召开部门
sheet.Cells[2, 4].PutValue(cr.Compere); //主持人
sheet.Cells[3, 2].PutValue(cr.Content); //主要议题
if (SdUser.Length == 0)
{
sheet.Cells[5, 1].PutValue("无");
}
else
{
sheet.Cells[5 + SdUser.Length, 0].PutValue("已签到人数");
sheet.Cells.Merge(5, 0, SdUser.Length, 1);
foreach (DataRow item in SdUser)
{
index++;
sheet.Cells[4 + index, 1].PutValue(index);
sheet.Cells[4 + index, 2].PutValue(item[0].ToString()); //参会人员
sheet.Cells[4 + index, 4].PutValue(item[1].ToString()); //所属部门
if (!string.IsNullOrEmpty(item[2].ToString()))
{
string[] u = item[2].ToString().Split('/');
string url = path + "/" + u[1] + "/" + u[2] + "/" + u[3] + "/" + u[4] + "/" + u[5];//签名
FileStream fs = new FileStream(url, FileMode.Open);
int imgIndex = sheet.Pictures.Add(4 + index, 3, fs, 120, 30);
fs.Close();
sheet.Cells.SetColumnWidth(5, 20);
sheet.Cells.SetRowHeight(4 + index, 44);
sheet.Pictures[imgIndex].Width = 139;
sheet.Pictures[imgIndex].Height = 50;
sheet.Pictures[imgIndex].Top = 3;
sheet.Pictures[imgIndex].Left = 6;
}
}
}
int num = SdUser.Length == 0 ? 1 : SdUser.Length;
int s = SdUser.Length == 0 ? 1 : SdUser.Length;
if (index > 0)
{
s = 0;
}
if (YdUser.Length == 0)
{
sheet.Cells[5 + num, 0].PutValue("未签到人数");
sheet.Cells[5 + num, 1].PutValue("无");
}
else
{
sheet.Cells[5 + num, 0].PutValue("未签到人数");
sheet.Cells.Merge(5 + num, 0, YdUser.Length, 1);
foreach (DataRow item in YdUser)
{
index++;
sheet.Cells[4 + s + index, 1].PutValue(index);
sheet.Cells[4 + s + index, 2].PutValue(item[0].ToString());
sheet.Cells[4 + s + index, 4].PutValue(item[1].ToString());
sheet.Cells[4 + s + index, 3].PutValue("");
}
}
s = YdUser.Length == 0 ? 1 : YdUser.Length;
int qj = s + SdUser.Length == 0 ? 1 : s + SdUser.Length;
qj = SdUser.Length == 0 ? qj + 1 : qj;
int qjNum = 0;
if (QUser.Length > 0)
{
sheet.Cells[5 + qj, 0].PutValue("请假人数");
sheet.Cells.Merge(5 + qj, 0, QUser.Length, 1);
foreach (DataRow dr in QUser)
{
sheet.Cells[5 + qj + qjNum, 1].PutValue(index++ + 1);
sheet.Cells[5 + qj + qjNum, 2].PutValue(dr[0].ToString());
sheet.Cells[5 + qj + qjNum, 4].PutValue(dr[1].ToString());
sheet.Cells[5 + qj + qjNum, 3].PutValue("");
qjNum++;
}
}
else
{
qjNum += 1;
sheet.Cells[5 + qj, 0].PutValue("请假人数");
sheet.Cells[5 + qj, 1].PutValue("无");
}
Aspose.Cells.Range r = sheet.Cells.CreateRange(5, 0, qj + qjNum, 5);
r.Style = style;
HttpResponse resp = System.Web.HttpContext.Current.Response;
wk.Save(HttpUtility.UrlEncode(fileName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInExcel, resp);
return(ToJsonResult("0"));
}
catch (Exception ex)
{
string env = Config.GetValue("Environment");
string msg = env == "dev" ? ex.Message : "对不起,系统出错了";
return(ToJsonResult("" + msg + ""));
}
}
