public static bool DataTableToExcel2_sheet0(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
// style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}
for (int k = 0; k < datatable.Columns.Count; k++)
{
wb.Worksheets[0].AutoFitColumn(k, 0, 150);
}
wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
wb.Save(filepath);
return(true);
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
private void BindCheckItem(ref Aspose.Cells.Worksheet sheet, string FormNo, string Bu)
{
//page 格式設定
SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape);
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
NPIMgmt oMgmt = new NPIMgmt("CZ", Bu);
NPI_Standard oStandard = oMgmt.InitialLeaveMgmt();
#region//獲取主表資訊
DataTable dtMaster = oStandard.GetPrelaunchInconformity(FormNo);
if (dtMaster.Rows.Count > 0)
{
int templateIndex = 1; //模板row起始位置
int insertIndexEnCounter = templateIndex + 1; //new row起始位置
cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1);
cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行
string url = "http://icm651.liteon.com/WF_PrelaunchReport/";
for (int i = 0; i < dtMaster.Rows.Count; i++)
{
DataRow dr = dtMaster.Rows[i];
string FileName = dr["FileName"].ToString();
cells[i + templateIndex, 0].PutValue(dr["ID"].ToString());
cells[i + templateIndex, 1].PutValue(dr["Dept"].ToString());
cells[i + templateIndex, 2].PutValue(dr["CheckItem"].ToString());
cells[i + templateIndex, 3].PutValue(dr["Description"].ToString());
cells[i + templateIndex, 4].PutValue(dr["Status"].ToString());
cells[i + templateIndex, 5].PutValue(dr["Remark"].ToString());
cells[i + templateIndex, 6].PutValue(dr["Suggestion"].ToString());
cells[i + templateIndex, 7].PutValue(dr["CompleteDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompleteDate"].ToString()).ToString("yyyy/MM/dd") : dr["CompleteDate"].ToString());
cells[i + templateIndex, 8].PutValue(dr["UpateUser"].ToString());
cells[i + templateIndex, 9].PutValue(dr["UpdateTime"].ToString().Length > 0 ? Convert.ToDateTime(dr["UpdateTime"].ToString()).ToString("yyyy/MM/dd") : dr["UpdateTime"].ToString());
if (!string.IsNullOrEmpty(FileName))
{
string destFileName = url + dr["AttacheFile"].ToString();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
cells[i + templateIndex, 10].PutValue(FileName);
cells[i + templateIndex, 10].SetStyle(style);
sheet.Hyperlinks.Add(i + templateIndex, 10, 1, 1, destFileName);
cells.Merge(i + templateIndex, 10, 1, 3);
}
}
}
#endregion
}
/// <summary>
/// 调用AsposeCell控件，生成Excel文件
/// </summary>
/// <param name="datatable">生成的表格数据</param>
/// <param name="relatedPath">服务器相对路径</param>
/// <returns></returns>
protected virtual bool GenerateExcel(DataTable datatable, string relatedPath)
{
#region 把DataTable转换为Excel并输出
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
//为单元格添加样式
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
style.Pattern = Aspose.Cells.BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
workbook.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
workbook.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
workbook.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}
for (int k = 0; k < datatable.Columns.Count; k++)
{
workbook.Worksheets[0].AutoFitColumn(k, 0, 150);
}
workbook.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
//根据用户创建目录，确保生成的文件不会产生冲突
string realPath = Server.MapPath(relatedPath);
string parentPath = Directory.GetParent(realPath).FullName;
DirectoryUtil.AssertDirExist(parentPath);
workbook.Save(realPath, Aspose.Cells.SaveFormat.Excel97To2003);
#endregion
return(true);
}
/// <summary>
/// 将DataTable，导出为Excel
/// </summary>
/// <param name="dt">DataTable对象</param>
/// <param name="path">存储路径</param>
/// <param name="sheet">Sheet名称</param>
/// <returns></returns>
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path, string sheet)
{
bool succeed = false;
if (dt != null)
{
try
{
// Excel
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = sheet;
// 开始行、结束行；开始列、结束列
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
// 列标题
for (int i = 0; i < colCount; i++)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
s.Font.IsBold = true;
cellSheet.Cells[0, i].SetStyle(s);
cellSheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
// 单元格数据
for (int i = 1; i <= rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[i, j].PutValue(dt.Rows[i - 1][j].ToString());
}
}
cellSheet.AutoFitColumns();
path = System.IO.Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
succeed = false;
}
}
return(succeed);
}
public MemoryStream ToExcel <T>(IPaginatedList <T> list)
{
try
{
//Create excel workbook
Aspose.Cells.Workbook book = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
List <string> objHeaders = new List <string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
//Avoid columns from Base Business Entity
if (property.Name == "ChangedFields")
{
break;
}
objHeaders.Add(property.Name);
}
sheet.Cells.ImportCustomObjects(list.Items.ToList(), objHeaders.ToArray(), true, 1, 0, list.TotalRecordCount, true, string.Empty, false);
#region Header style
Aspose.Cells.Style style = book.CreateStyle();
style.Font.IsBold = true;
// Define a style flag struct.
Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.FontBold = true;
// Get the first row in the first worksheet.
Aspose.Cells.Row row = book.Worksheets[0].Cells.Rows[1];
// Apply the style to it.
row.ApplyStyle(style, flag);
#endregion
// Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
using (MemoryStream outputStream = new MemoryStream())
{
//Save workbook as stream instead of saving it to the Disk
book.Save(outputStream, Aspose.Cells.SaveFormat.Xlsx);
//Call dispose methods
sheet.Dispose();
book.Dispose();
outputStream.Position = 0;
return(outputStream);
}
}
catch (Exception)
{
// TODO : Handle out of memory exception
throw;
}
}
/// <summary>
/// 导出到Excel文件
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="path">保存路径</param>
/// <returns></returns>
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.License li = new Aspose.Cells.License();
//设置破解DLL的许可证
li.SetLicense(Application.StartupPath + @"\Resources\License.lic");
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 10;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
path = Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception)
{
succeed = false;
}
}
return(succeed);
}
/// <summary>
/// DataTable 导出Excel
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">filepath</param>
/// <param name="error">错误信息</param>
/// <returns>bool</returns>
public static bool DataTableToExcel(System.Data.DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
cells[nRow, i].PutValue(datatable.Columns[i].ColumnName);
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Font.IsBold = true;
style.Font.Size = 11;
cells[nRow, i].SetStyle(style);
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return(true);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string path)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.License li = new Aspose.Cells.License();
string lic = @"<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>";
Stream s = new MemoryStream(ASCIIEncoding.Default.GetBytes(lic));
li.SetLicense(s);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = "Sheet1";
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 10;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
path = Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return(succeed);
}
public static bool ExportExcelWithAspose(DataSet ds, string path)
{
bool succeed = false;
if (ds != null && ds.Tables.Count > 0)
{
try
{
Aspose.Cells.License li = new Aspose.Cells.License();
var lic = Resources.License;
Stream s = new MemoryStream(lic);
li.SetLicense(s);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Worksheets.Clear();
for (int tableCount = 0; tableCount < ds.Tables.Count; tableCount++)
{
workbook.Worksheets.Add(ds.Tables[tableCount].TableName);
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[tableCount];
// cellSheet.Name = ds.Tables[tableCount].TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = ds.Tables[tableCount].Columns.Count;
int rowCount = ds.Tables[tableCount].Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Columns[i].ColumnName);
cellSheet.Cells[rowIndex, colIndex].Style.Font.IsBold = true;
cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Arial";
style.Font.Size = 10;
Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(ds.Tables[tableCount].Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
}
path = Path.GetFullPath(path);
workbook.Save(path);
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return(succeed);
}
//internal void RectangleExport()
//{
// throw new NotImplementedException();
//}
/// <summary>
/// 导出方法
/// </summary>
/// <param name="DataGridView"></param>
/// <param name="dt"></param>
/// <param name="Export"></param>
public void DataGridViewToExcel(DataGridView DataGridView, int Export = 0)
{
if (DataGridView.Rows.Count < 1)
{
return;
}
int i = 0;
int Col = 0;
ArrayList colList = new ArrayList(); //存放不显示的列
Aspose.Cells.Workbook xlBook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet xlSheet = default(Aspose.Cells.Worksheet);
string filepath = "";
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Font.Name = "宋体";
Aspose.Cells.Style stylecolor = new Aspose.Cells.Style();
stylecolor.ForegroundColor = Color.LightBlue;
Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.Font = true;
xlSheet = xlBook.Worksheets[0];
//intExcelTempIndex += 1;
//xlSheet.Name = "牙模盒使用记录" + intExcelTempIndex.ToString();
int Rowi = 0;
int Rown = 0;
int RownMax = 0;
//最后Export列不导出
RownMax = System.Convert.ToInt32(DataGridView.Columns.Count - Export);
//填写表头
for (i = 0; i < RownMax; i++)
{
if (DataGridView.Columns[i].Width <= 5 || DataGridView.Columns[i].Visible == false)
{
}
else
{
colList.Add(i);
xlSheet.Cells[0, Col].PutValue(DataGridView.Columns[i].HeaderText);
Col++;
}
}
//表内容
int intcount = 0;
for (Rowi = 0; Rowi <= DataGridView.Rows.Count - 1; Rowi++)
{
if (DataGridView.Rows[Rowi].Visible == false)
{
intcount++;
continue;
}
for (Rown = 0; Rown <= colList.Count - 1; Rown++)
{
if (DataGridView.Rows[Rowi].Cells[Rown].Value != null)
{
xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue(DataGridView.Rows[Rowi].Cells[Rown].Value.ToString());
}
else
{
xlSheet.Cells[Rowi + 1 - intcount, Rown].PutValue("");
}
}
}
xlSheet.Cells.ApplyStyle(style, flag);
if (DataGridView.Rows.Count > 60000)
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xlsx";
}
else
{
filepath = System.Windows.Forms.Application.StartupPath + "\\" + xlSheet.Name + ".xls";
}
xlBook.Save(filepath);
System.Diagnostics.Process.Start(filepath); //'打开导出Excel
}
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 + ""));
}
}
/// <summary>
/// DataTable 导出Excel
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">filepath</param>
/// <param name="error">错误信息</param>
/// <returns>bool</returns>
public static bool DataTableToExcel(System.Data.DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
cells[nRow, i].PutValue(datatable.Columns[i].ColumnName);
Aspose.Cells.Style style = new Aspose.Cells.Style();
style.Font.IsBold=true;
style.Font.Size = 11;
cells[nRow, i].SetStyle(style);
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
{
//------插入图片数据-------
System.Drawing.Image image = (System.Drawing.Image)row[i];
MemoryStream mstream = new MemoryStream();
image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
sheet.Pictures.Add(nRow, i, mstream);
}
else
{
cells[nRow, i].PutValue(row[i]);
}
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath);
return true;
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}
public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Worksheets.Clear();
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
//style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
//新建立sheet页
int count = datatable.Rows.Count;
int totalNum = datatable.Rows.Count / EXCEL13_MaxRow + 1;//总共生成的sheet页
string sheetName = "";
int beginIndex = 0;
int endIndex = 0;
for (int n = 0; n < totalNum; n++)
{
sheetName = "sheet" + n.ToString();
wb.Worksheets.Add(sheetName);
rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[n].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[n].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
beginIndex = n * EXCEL13_MaxRow;
endIndex = beginIndex + EXCEL13_MaxRow;
if (endIndex >= count)
{
endIndex = count;
}
if (beginIndex < count && endIndex <= count)
{
for (int j = beginIndex; j < endIndex; j++)
{
DataRow row = datatable.Rows[j];
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[n].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}
}
for (int k = 0; k < datatable.Columns.Count; k++)
{
wb.Worksheets[n].AutoFitColumn(k, 0, 150);
}
wb.Worksheets[n].FreezePanes(1, 0, 1, datatable.Columns.Count);
}
//创建文件
FileStream file = new FileStream(filepath, FileMode.Create);
//关闭释放流，不然没办法写入数据
file.Close();
file.Dispose();
wb.Save(filepath);
wb = null;
return(true);
}
catch (Exception e)
{
for (int i = 0; i < datatable.Rows.Count / EXCEL13_MaxRow + 1; i++)
{
wb.Worksheets[i].Cells.Clear();
}
wb.Worksheets.Clear();
wb = null;
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
/// <summary>
/// ASPOSE.CELL方式将DataTable导出excel（不依赖office组件）
/// </summary>
/// <param name="dt">DataTable数据</param>
/// <param name="path">保存到物理文件全路径</param>
/// <param name="hasDownload">是否要同时产生下载流操作</param>
/// <param name="fileName">下载到客户端保存的文件名（文件名+扩展名）</param>
/// <returns>是否导出成功</returns>
public static bool ExportExcelWithAspose(System.Data.DataTable dt, string fileName = "", string path = "", bool hasDownload = true)
{
bool succeed = false;
if (dt != null)
{
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center;
//style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//style.ShrinkToFit = true;
//style.Font.Name = "Arial";
//style.Font.Size = 12;
//Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag();
//cellSheet.Cells.ApplyStyle(style, styleFlag);
//列名的处理
for (int i = 0; i < colCount; i++)
{
Aspose.Cells.Style style1 = new Aspose.Cells.Style();
style1.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center;
if (i == 0)
{
style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left;
}
else
{
style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
}
style1.ShrinkToFit = true;
style1.Font.Name = "Arial";
style1.Font.Size = 15;
style1.Font.IsBold = true;
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
cellSheet.Cells[rowIndex, colIndex].SetStyle(style1);
//cellSheet.Cells[rowIndex, colIndex].SetStyle( .Style.Font.IsBold = true;
//cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "微软雅黑";
colIndex++;
}
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
Aspose.Cells.Style style1 = new Aspose.Cells.Style();
style1.VerticalAlignment = Aspose.Cells.TextAlignmentType.Center;
if (colIndex == 0)
{
style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Left;
}
else
{
style1.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
}
style1.ShrinkToFit = true;
style1.Font.Name = "Arial";
style1.Font.Size = 12;
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
cellSheet.Cells[rowIndex, colIndex].SetStyle(style1);
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
if (string.IsNullOrEmpty(path))
{
path = HttpContext.Current.Server.MapPath("/Log/Error/" + Guid.NewGuid() + ".xls");
}
if (!Directory.Exists(Path.GetDirectoryName(path)))
{
Directory.CreateDirectory(Path.GetDirectoryName(path));
}
path = Path.GetFullPath(path);
workbook.Save(path);
try
{
if (hasDownload)
{
if (string.IsNullOrEmpty(fileName))
{
fileName = Guid.NewGuid() + ".xls";
}
if (string.IsNullOrEmpty(Path.GetExtension(fileName)))
{
fileName = fileName + ".xls";
}
if (File.Exists(path))
{
DownloadFileV4(HttpContext.Current.Response, path, fileName, true);
}
}
}
catch (Exception Ex2)
{
}
succeed = true;
}
catch (Exception ex)
{
succeed = false;
}
}
return(succeed);
}
