private void populateMentees(string path, RoleType rType)
{
OfficeOpenXml.ExcelWorksheet ws = readExcel(path);
for (int rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)//foreach mentee
{
var wsRow = ws.Cells[rowNum, ws.Dimension.End.Column];
if (wsRow["B" + rowNum].Text.Trim() == string.Empty)
{
continue;
}
Mentee objMentee = new Mentee();
objMentee.Name = wsRow["B" + rowNum].Text.Trim();
objMentee.Dept = setDept(wsRow["C" + rowNum].Text.Trim());
objMentee.UscId = wsRow["D" + rowNum].Text.Trim();
objMentee.Contact = wsRow["E" + rowNum].Text.Trim();
if (!_dicMenteeDepartment.ContainsKey(objMentee.Dept))
{
List <Mentee> lstMentees = new List <Mentee>();
lstMentees.Add(objMentee);
_dicMenteeDepartment.Add(objMentee.Dept, lstMentees);
}
else
{
List <Mentee> lstMentees = _dicMenteeDepartment[objMentee.Dept];
lstMentees.Add(objMentee);
}
}
}
private void cmdImport_tabTILSOFTFile_Click(object sender, EventArgs e)
{
string uniqueFileName = Path.GetTempPath() + @"\" + System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx";
string pointer = string.Empty;
System.IO.FileInfo epFInfo = new System.IO.FileInfo(uniqueFileName);
OfficeOpenXml.ExcelPackage ePackagae = new OfficeOpenXml.ExcelPackage(epFInfo);
OfficeOpenXml.ExcelWorksheet pWS = ePackagae.Workbook.Worksheets.Add("Result");
List <DTO.FilePointer> result = new List <DTO.FilePointer>();
foreach (FileInfo fInfo in (new DirectoryInfo(FrameworkSetting.Setting.AbsoluteReportTmpFolder)).GetFiles())
{
pointer = string.Empty;
DTO.FilePointer fPointer = new DTO.FilePointer()
{
PhysicalFileName = fInfo.Name
};
try
{
fPointer.FilePointerUD = fwFactory.CreateFilePointer(FrameworkSetting.Setting.AbsoluteReportTmpFolder, fInfo.Name, string.Empty);
fwFactory.CreateImageCache(fPointer.FilePointerUD, 120, 120, false);
result.Add(fPointer);
}
catch (Exception ex)
{
}
}
pWS.Cells["A1"].LoadFromCollection(result, true);
ePackagae.Save();
System.Diagnostics.Process.Start(uniqueFileName);
}
public static void VeLogo(string name, OfficeOpenXml.ExcelWorksheet ws)
{
var p = ws.Drawings.AddPicture(name, Resources.CNS);
p.SetPosition(0, 0, 0, 0);
p.SetSize(100);
}
private List <ImportModel.rawImport> ReadInputFile()
{
List <ImportModel.rawImport> recordsIn = new List <ImportModel.rawImport>();
var fred = TempData["path"].ToString();
//var filesData = Directory.GetFiles(@fred);
string path = Server.MapPath("~/App_Data/" + fred);
//string path = Server.MapPath(fred.ToString());
var package = new OfficeOpenXml.ExcelPackage(new FileInfo(path));
OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[1];
for (int row = workSheet.Dimension.Start.Row;
row <= workSheet.Dimension.End.Row;
row++)
{
if (ExcelData.HasData(workSheet, row))
{
decimal price = 0.0m;
ImportModel.rawImport obj = new ImportModel.rawImport();
obj.Sku = ExcelData.GetPBSKU(workSheet, row);
obj.FormSizeCode = ExcelData.GetPBFSCOde(workSheet, row);
obj.Name = ExcelData.GetName(workSheet, row);
obj.FormSize = ExcelData.GetFSDecription(workSheet, row);
price = ExcelData.GetPrice(workSheet, row);
obj.FromDate = null;
// obj.FromDate = ExcelData.GetDate(workSheet, row);
obj.Price = price * 100;
obj.Location = "PB";
recordsIn.Add(obj);
}
}
return(recordsIn);
}
public void Export(TreeGenerative tree, string path)
{
FileInfo fi = new FileInfo(path);
if (fi.Exists)
{
fi.Delete();
}
_featureNames = tree.Root.Set.GetFeatureNames();
_resolutionFeatureName = tree.ResolutionFeatureName;
using (OfficeOpenXml.ExcelPackage xls = new OfficeOpenXml.ExcelPackage(fi))
{
OfficeOpenXml.ExcelWorksheet sheet = xls.Workbook.Worksheets.Add("Classified");
for (int j = 0; j < _featureNames.Count; j++)
{
sheet.Cells[1, j + 1].Value = _featureNames[j];
}
sheet.Cells[1, _featureNames.Count + 1].Value = "Category";
sheet.Cells[1, _featureNames.Count + 2].Value = string.Format("Delta({0})", _resolutionFeatureName);
ExportRecursion(tree.Root, sheet);
xls.Save();
}
var exportCompleted = ExportCompleted;
if (exportCompleted != null)
{
exportCompleted(this, fi.Name);
}
}
public static void ExportExcelByEPPlus(HttpContext context, DataTable dt, string fileName, string headerName)
{
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
string sheetName = string.IsNullOrEmpty(dt.TableName) ? "sheet1" : dt.TableName;
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
ws.Cells[2, 1].LoadFromDataTable(dt, true);//从A4的单元格加载datatable中的数据
OfficeOpenXml.Style.ExcelBorderStyle borderStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
System.Drawing.Color borderColor = System.Drawing.Color.FromArgb(0, 0, 0);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 2, dt.Columns.Count])
{
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = 11;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
rng.Style.Border.Top.Style = borderStyle;
rng.Style.Border.Top.Color.SetColor(borderColor);
rng.Style.Border.Bottom.Style = borderStyle;
rng.Style.Border.Bottom.Color.SetColor(borderColor);
rng.Style.Border.Right.Style = borderStyle;
rng.Style.Border.Right.Color.SetColor(borderColor);
}
//Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count]) //1行1列到1行n列
{
rng.Merge = true; //合并单元格
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0));
rng.Value = headerName;
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, 2, dt.Columns.Count])//1行1列到1行n列
{
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0));
}
//返回到客户端
context.Response.Clear();
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.BinaryWrite(pck.GetAsByteArray());
context.Response.Flush();
context.ApplicationInstance.CompleteRequest();//解决捕获的“由于代码已经过优化...”的try catch异常
//HttpContext.Current.Response.End();
}
}
public ExcelImport(OfficeOpenXml.ExcelWorksheet workSheet, ContentTypeData typeData)
{
InitializeComponent();
this.WorkSheet = workSheet;
this.TypeData = typeData;
Load += ExcelImport_Load;
}
public byte[] ExportCSV(string path, List <ProductDTO> data)
{
string uniqueFileName = FrameworkSetting.Setting.AbsoluteReportTmpFolder + System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx";
System.IO.FileInfo fInfo = new System.IO.FileInfo(uniqueFileName);
OfficeOpenXml.ExcelPackage ePackagae = new OfficeOpenXml.ExcelPackage(fInfo);
OfficeOpenXml.ExcelWorksheet pWS = ePackagae.Workbook.Worksheets.Add("Product");
// add data to product sheet
var products = from o in data
select new
{
sku = o.ArticleCode
, name = o.ModelNM + " (" + o.MaterialColorNM + ")"
, description = o.Description
, product_type = o.ProductTypeNM
, type = o.ProductTypeNM == "SET" ? "bundle" : "simple"
, weight = o.NetWeight
, price = o.UnitPrice
, article_code = o.ArticleCode
, sub_ean_code = o.SubEANCode
, material_fs = o.MaterialNM
, color_fs = o.MaterialColorNM
, material_type = o.MaterialTypeNM
, frame_material = o.FrameMaterialNM
, frame_material_color = o.FrameMaterialColorNM
, back_cushion = o.BackCushionNM
, seat_cushion = o.SeatCushionNM
, cushion_color = o.CushionColorNM
, height = o.OverallDimH + "cm"
, length = o.OverallDimL + "cm"
, width = o.OverallDimW + "cm"
, seat_height = o.SeatCushionDimH
, seat_length = o.SeatCushionDimL
, seat_width = o.SeatCushionDimW
, wxlxh_cm = o.OverallDimW + "cm x " + o.OverallDimL + "cm x " + o.OverallDimH + "cm"
, carton_box_length = o.CartonBoxDimL
, carton_box_height = o.CartonBoxDimH
, carton_box_width = o.CartonBoxDimW
, qty = o.StockQnt
};
pWS.Cells["A1"].LoadFromCollection(products.ToList(), true);
ePackagae.Save();
List <Library.DTO.ZipFileDTO> toBeZippedFiles = new List <Library.DTO.ZipFileDTO>();
toBeZippedFiles.Add(new Library.DTO.ZipFileDTO()
{
FileName = "product", FilePath = uniqueFileName
});
data.Where(o => !string.IsNullOrEmpty(o.FileLocation)).ToList().ForEach(o => toBeZippedFiles.Add(new Library.DTO.ZipFileDTO()
{
FileName = o.ArticleCode, FilePath = FrameworkSetting.Setting.AbsoluteFileFolder + o.FileLocation
}));
Library.FileHelper.ZipManager zMng = new Library.FileHelper.ZipManager(FrameworkSetting.Setting.AbsoluteReportTmpFolder);
return(File.ReadAllBytes(zMng.CreateZipFile(toBeZippedFiles)));
}
private object getExcelGateValue(OfficeOpenXml.ExcelWorksheet pWorkSheet, int intRow, enumExcelGateColumn pCol)
{
if (pWorkSheet == null)
{
return(null);
}
return(pWorkSheet.GetValue(intRow, (int)pCol));
}
public static void SaveExcelFromDataTable(string path, DataTable dataTable)
{
// https://stackoverflow.com/questions/13669733/export-datatable-to-excel-with-epplus
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(new FileInfo(path)))
{
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
pck.Save();
}
}
/// <summary>
/// 创建修订日志sheet
/// </summary>
/// <param name="epck"></param>
/// <param name="sheetName"></param>
/// <param name="tables"></param>
private static void CreateLogSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables)
{
OfficeOpenXml.ExcelWorksheet overviewTbWorksheet = epck.Workbook.Worksheets.Add(sheetName);
int row = 1;
overviewTbWorksheet.Cells[row, 1, row, 5].Merge = true;
//overviewTbWorksheet.Cells[row, 1].Value = "总表数量";
//overviewTbWorksheet.Cells[row, 2].Value = tables.Count + "";
//overviewTbWorksheet.Cells[row, 4].Value = "密码等级";
//overviewTbWorksheet.Cells[row, 5].Value = "秘密";
row++; // 行号+1
overviewTbWorksheet.Cells[row, 1].Value = "版本号";
overviewTbWorksheet.Cells[row, 2].Value = "修订日期";
overviewTbWorksheet.Cells[row, 3].Value = "修订内容";
overviewTbWorksheet.Cells[row, 4].Value = "修订人";
overviewTbWorksheet.Cells[row, 5].Value = "审核人";
overviewTbWorksheet.Cells[row, 1, row, 5].Style.Font.Bold = true;
overviewTbWorksheet.Cells[row, 1, row, 5].Style.Font.Size = 10;
overviewTbWorksheet.Row(1).Height = 20; // 行高
// TODO 循环日志记录
row++; // 行号+1
for (var i = 0; i < 16; i++)
{
// TODO 添加列标题
overviewTbWorksheet.Cells[row, 1].Value = "";
overviewTbWorksheet.Cells[row, 2].Value = "";
overviewTbWorksheet.Cells[row, 3].Value = "";
overviewTbWorksheet.Cells[row, 4].Value = "";
overviewTbWorksheet.Cells[row, 5].Value = "";
overviewTbWorksheet.Row(row).Height = 20; // 行高
row++; // 行号+1
}
// TODO 水平居中
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// TODO 垂直居中
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
// TODO 上下左右边框线
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Column(1).Width = 25;
overviewTbWorksheet.Column(2).Width = 25;
overviewTbWorksheet.Column(3).Width = 50;
overviewTbWorksheet.Column(4).Width = 25;
overviewTbWorksheet.Column(5).Width = 25;
}
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo)
{
worksheet.Cells["B2"].Value = inputInfo.Region;
worksheet.Cells["B3"].Value = inputInfo.Province;
worksheet.Cells["B4"].Value = inputInfo.City;
worksheet.Cells["B5"].Value = inputInfo.Market;
worksheet.Cells["B6"].Value = inputInfo.StoreName;
worksheet.Cells["B7"].Value = inputInfo.USCode;
worksheet.Cells["B8"].Value = inputInfo.OpenDate.ToString("yyyy-MM-dd");
worksheet.Cells["B9"].Value = inputInfo.LeaseExpirationDate.ToString("yyyy-MM-dd");
worksheet.Cells["B10"].Value = inputInfo.Priority;
}
public static string ReadCellAsString(OfficeOpenXml.ExcelWorksheet worksheet, int rowNum, int columnNum)
{
object cellValue = ReadCell(worksheet, rowNum, columnNum);
if (cellValue == null)
{
return("");
}
else
{
return(cellValue.ToString());
}
}
public ActionResult ExportToExcel()
{
T_Expense ObjPurchaseOrder = new T_Expense();
var vObjList = _blExpense.GetList(ObjPurchaseOrder);
// ExcelPackageF dll Need
OfficeOpenXml.ExcelPackage Ep = new OfficeOpenXml.ExcelPackage();
OfficeOpenXml.ExcelWorksheet workSheet = Ep.Workbook.Worksheets.Add("Report");
string sEndColumn = "E1";
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Font.Bold = true;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightSeaGreen);
var allCells = workSheet.Cells[string.Format("A1:{0}", sEndColumn)];
var cellFont = allCells.Style.Font;
workSheet.Cells["A1"].Value = "Date";
//sheet["A3:A4"].Merge();
workSheet.Cells["B1"].Value = "Enpense Type";
workSheet.Cells["C1"].Value = "Amount";
workSheet.Cells["D1"].Value = "Remark";
workSheet.Cells["E1"].Value = "Create Date";
string dateformat = "dd-MM-yyyy";
int row = 2;
foreach (var item in vObjList)
{
workSheet.Cells[string.Format("A{0}", row)].Value = item.EventDate;
workSheet.Cells[string.Format("A{0}", row)].Style.Numberformat.Format = dateformat;
workSheet.Cells[string.Format("B{0}", row)].Value = item.ExpenseTypeName;
workSheet.Cells[string.Format("C{0}", row)].Value = item.Amount;
workSheet.Cells[string.Format("D{0}", row)].Value = item.Remark;
workSheet.Cells[string.Format("E{0}", row)].Value = item.CreatedDate;
workSheet.Cells[string.Format("E{0}", row)].Style.Numberformat.Format = dateformat;
row++;
}
workSheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "Expense.csv" + DateTime.Now.ToString("s"));
Response.BinaryWrite(Ep.GetAsByteArray());
Response.End();
return(View());
}
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo)
{
string strRow = (3 + RowNumber).ToString();
worksheet.Cells["A" + strRow].Value = (RowNumber + 1).ToString();
worksheet.Cells["B" + strRow].Value = inputInfo.McdParticipants;
worksheet.Cells["C" + strRow].Value = inputInfo.Content;
worksheet.Cells["D" + strRow].Value = inputInfo.LLParticipants;
worksheet.Cells["E" + strRow].Value = inputInfo.Topic;
worksheet.Cells["F" + strRow].Value = inputInfo.Location;
worksheet.Cells["G" + strRow].Value = inputInfo.MeetingDate;
worksheet.Cells["H" + strRow].Value = inputInfo.CreateDate;
RowNumber += 1;
}
public static void GhiThongTinTongcty(OfficeOpenXml.ExcelWorksheet ws, string address,
int sR1, int sC1, int sizeTCty, int sR2, int sC2, int sizeDiaChi, int sR3, int sC3)
{
XL.FormatCells(ws.Cells[sR1 + 0, sC1], "TỔNG CÔNG TY CÔNG NGHIỆP SÀI GÒN", VeBorder: false, size: sizeTCty);
XL.FormatCells(ws.Cells[sR1 + 1, sC1], "TRÁCH NHIỆM HỮU HẠN MỘT THÀNH VIÊN", VeBorder: false, size: sizeTCty);
XL.FormatCells(ws.Cells[sR1 + 2, sC1], "NHÀ MÁY THUỐC LÁ KHÁNH HỘI", VeBorder: false, bold: true, size: sizeTCty);
XL.FormatCells(ws.Cells[sR2 + 0, sC2], "Lô 26, đường số 3, KCN Tân Tạo, Quận Bình Tân, Thành phố Hồ Chí Minh", VeBorder: false, size: sizeDiaChi);
XL.FormatCells(ws.Cells[sR2 + 1, sC2], "Tel: +848 37507282 - FAX: 37507784", VeBorder: false, size: sizeDiaChi);
XL.FormatCells(ws.Cells[sR2 + 2, sC2], "Email: [email protected] Website: http://khcig.com", VeBorder: false, size: sizeDiaChi);
var tphcm = "Thành phố Hồ Chí Minh, ngày {0} tháng {1} năm {2}";
tphcm = string.Format(tphcm, DateTime.Today.Day, DateTime.Today.Month, DateTime.Today.Year);
XL.FormatCells(ws.Cells[sR3, sC3], tphcm, VeBorder: false, size: sizeDiaChi);
}
public static string GetValue(OfficeOpenXml.ExcelWorksheet worksheet, int row, int col)
{
if (worksheet.Cells[row, col].Value != null)
{
var value = worksheet.Cells[row, col].Value;
Type t = value.GetType();
if (t.Equals(typeof(DateTime)))
{
DateTime date = (DateTime)value;
var strDate = date.Day + "/" + date.Month + "/" + date.Year + " " + date.Hour + ":" + date.Minute;
return(strDate);
}
else
{
return(worksheet.Cells[row, col].Value.ToString().Trim());
}
}
return(string.Empty);
}
public static string CreateExcelFileWithEPPlus <Unknow>(List <Unknow> data)
{
string result = string.Empty;
string uniqueFileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx";
System.IO.FileInfo f = new System.IO.FileInfo(Setting.AbsoluteReportTmpFolder + uniqueFileName);
if (f.Exists)
{
f.Delete();
}
using (OfficeOpenXml.ExcelPackage ep = new OfficeOpenXml.ExcelPackage(f))
{
OfficeOpenXml.ExcelWorksheet eSheet = ep.Workbook.Worksheets.Add("Data");
eSheet.Cells[2, 1].LoadFromCollection(data);
ep.Save();
}
result = uniqueFileName;
return(result);
}
private byte[] CreateExcelStream()
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
using (System.IO.FileStream fs = System.IO.File.OpenRead(System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "../PlanilhaDeEntrada.xlsx"))
{
using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(fs))
{
OfficeOpenXml.ExcelWorkbook excelWorkBook = excelPackage.Workbook;
OfficeOpenXml.ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First();
excelWorksheet.Cells[15, 2].Value = "Ou 'B1'";
excelWorksheet.Cells[15, 3].Value = "Ou 'B2'";
excelWorksheet.Cells[15, 4].Value = "Ou 'B3'";
excelWorksheet.Cells[15, 5].Value = "Ou 'B4'";
excelWorksheet.Cells[15, 6].Value = "Ou 'B5'";
excelWorksheet.Cells[15, 7].Value = "Ou 'B6'";
excelPackage.SaveAs(ms); // This is the important part.
return(ms.ToArray());
}
}
}
/// <summary>
/// 创建表目录sheet
/// </summary>
/// <param name="epck"></param>
/// <param name="sheetName"></param>
/// <param name="tables"></param>
private static void CreateOverviewSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables)
{
OfficeOpenXml.ExcelWorksheet overviewTbWorksheet = epck.Workbook.Worksheets.Add(sheetName);
int row = 1;
overviewTbWorksheet.Cells[row, 1].Value = "序号";
overviewTbWorksheet.Cells[row, 2].Value = "表名";
overviewTbWorksheet.Cells[row, 3].Value = "注释/说明";
overviewTbWorksheet.Cells[row, 1, row, 3].Style.Font.Bold = true;
overviewTbWorksheet.Cells[row, 1, row, 3].Style.Font.Size = 16;
overviewTbWorksheet.Row(1).Height = 30; // 行高
// TODO 循环数据库表名
row++;
foreach (var table in tables)
{
// TODO 数据库名称
// TODO 添加列标题
overviewTbWorksheet.Cells[row, 1].Value = table.TableOrder;
overviewTbWorksheet.Cells[row, 2].Value = table.TableName;
overviewTbWorksheet.Cells[row, 3].Value = (!string.IsNullOrWhiteSpace(table.Comment) ? table.Comment : "");
overviewTbWorksheet.Row(row).Height = 30; // 行高
row++; // 行号+1
}
// TODO 水平居中
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// TODO 垂直居中
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
// TODO 上下左右边框线
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
overviewTbWorksheet.Column(1).Width = 10;
overviewTbWorksheet.Column(2).Width = 50;
overviewTbWorksheet.Column(3).Width = 50;
}
private void ExportRecursion(NodeGenerative node, OfficeOpenXml.ExcelWorksheet sheet)
{
if (node == null)
{
return;
}
if (node.IsTerminal)
{
_isColored = !_isColored;
foreach (var item in node.Set.Items())
{
for (int i = 0; i < _featureNames.Count; i++)
{
sheet.Cells[_rowNo, i + 1].Value = item.GetValue(_featureNames[i]);
}
sheet.Cells[_rowNo, _featureNames.Count + 1].Value = node.Category;
double d = node.Average - item.GetValue(_featureNames[0]);
sheet.Cells[_rowNo, _featureNames.Count + 2].Value = d;
if (d < 0)
{
sheet.Cells[_rowNo, _featureNames.Count + 2].Style.Font.Color.SetColor(System.Drawing.Color.Red);
}
else if (d > 0)
{
sheet.Cells[_rowNo, _featureNames.Count + 2].Style.Font.Color.SetColor(System.Drawing.Color.Green);
}
if (_isColored)
{
sheet.Cells[_rowNo, 1, _rowNo, _featureNames.Count + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
sheet.Cells[_rowNo, 1, _rowNo, _featureNames.Count + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
}
_rowNo++;
}
return;
}
ExportRecursion(node.Left, sheet);
ExportRecursion(node.Right, sheet);
}
/// <summary>
/// This method is used to export the data to excel
/// </summary>
/// <param name="dataTable"></param>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static bool ExportToExcel(System.Data.DataTable dataTable, string filePath, string sheetName)
{
try
{
using (OfficeOpenXml.ExcelPackage xlPackage = new OfficeOpenXml.ExcelPackage())
{
if (File.Exists(filePath))
{
using (var stream = File.OpenRead(filePath))
{
xlPackage.Load(stream);
}
//delete existing worksheet if worksheet with same name already exist
OfficeOpenXml.ExcelWorksheet excelWorksheet = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetName);
if (excelWorksheet != null)
{
xlPackage.Workbook.Worksheets.Delete(sheetName);
}
excelWorksheet = xlPackage.Workbook.Worksheets.Add(sheetName);
excelWorksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
File.WriteAllBytes(filePath, xlPackage.GetAsByteArray());
}
else
{
var ws = xlPackage.Workbook.Worksheets.Add(sheetName);
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
xlPackage.SaveAs(new FileInfo(filePath));
}
}
return(true);
}
catch (Exception ex)
{
Reporter.ToLog(eLogLevel.ERROR, "Error Occurred while exporting to excel", ex);
return(false);
}
}
private void populateMentors(string path, RoleType rType)
{
OfficeOpenXml.ExcelWorksheet ws = readExcel(path);
for (int rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)//foreach mentor
{
var wsRow = ws.Cells[rowNum, ws.Dimension.End.Column];
Mentor objMentor = new Mentor();
objMentor.Name = wsRow["B" + rowNum].Text.Trim();
objMentor.Dept = setDept(wsRow["G" + rowNum].Text.Trim());
objMentor.UscId = wsRow["D" + rowNum].Text.Trim();
objMentor.Contact = wsRow["E" + rowNum].Text.Trim();
if (!_dicMentorDepartment.ContainsKey(objMentor.Dept))
{
List <Mentor> lstMentors = new List <Mentor>();
lstMentors.Add(objMentor);
_dicMentorDepartment.Add(objMentor.Dept, lstMentors);
}
else
{
List <Mentor> lstMentors = _dicMentorDepartment[objMentor.Dept];
lstMentors.Add(objMentor);
}
}
}
public static void SaveExcel(IEnumerable items, OfficeOpenXml.ExcelWorksheet sheet, Type elementType, string schemeAndAuthorityBase)
{
//var table = sheet.Tables.Single();
var headerRow = sheet.Row(1);
var fields = GetFields(elementType);
var nextRow = 2;
var col = 1;
foreach (var field in fields)
{
var cell = sheet.Cells[1, col];
cell.Value = field.Name;
col++;
var ft = field.Type;
var ti = ft.GetTypeInfo();
if (ti.IsGenericType && ft.GetGenericTypeDefinition() == typeof(Nullable <>))
{
ft = ft.GetGenericArguments()[0];
}
sheet.Column(col - 1).Width = ti.IsPrimitive || NumericTypes.Contains(ft) || ti.IsEnum || ft == typeof(DateTime) || ft == typeof(DateTimeOffset) ? 10 : 25;
}
foreach (var item in items)
{
var row = nextRow;
nextRow++;
col = 1;
foreach (var prop in fields)
{
var cell = sheet.Cells[row, col];
var val = prop.Get(item);
#if !STANDALONE
var es = val as EntitySet;
var ent = val as Entity;
if (es != null)
{
var url = (schemeAndAuthorityBase + Utils.GetHateosPath(es)).AsUri();
cell.Hyperlink = url;
cell.Value = url;
}
else if (ent != null)
{
var url = (schemeAndAuthorityBase + Utils.GetHateosPath(ent)).AsUri();
cell.Hyperlink = url;
cell.Value = ent.ToStringOrIdOrDefault(ent.EntityType.Name);
}
else
#endif
if (val is Uri)
{
var url = (Uri)val;
cell.Hyperlink = url;
cell.Value = url.AbsoluteUri;
//cell.Style.sy
}
#if !STANDALONE
else if (val is Money)
{
var money = (Money)val;
cell.Value = money.CurrencyValue;
if (money.CurrencyType != Currency.None)
{
cell.Style.Numberformat.Format = Money.GetExcelFormat(money.CurrencyType);
}
}
#endif
else
{
cell.Value = val;
if (val is DateTime || val is DateTimeOffset)
{
cell.Style.Numberformat.Format = "yyyy-mm-dd HH hh:mm";
}
}
col++;
}
}
var t = sheet.Tables.Add(new OfficeOpenXml.ExcelAddressBase(1, 1, nextRow - 1, col - 1), sheet.Name + "-Items");
t.TableStyle = OfficeOpenXml.Table.TableStyles.None;
}
public static IEnumerable <T> SaveExcel <T>(this IEnumerable <T> items, OfficeOpenXml.ExcelWorksheet sheet)
{
SaveExcel(items, sheet, typeof(T), null);
return(items);
}
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo)
{
worksheet.Cells["B2"].Value = inputInfo.USCode;
worksheet.Cells["B3"].Value = inputInfo.StoreNameEN;
worksheet.Cells["B4"].Value = inputInfo.StoreNameCN;
worksheet.Cells["B5"].Value = worksheet.Cells["B12"].Value = inputInfo.MinimarketPriority;
worksheet.Cells["B6"].Value = inputInfo.TADesrability;
worksheet.Cells["B7"].Value = inputInfo.MMTADescription;
worksheet.Cells["B8"].Value = inputInfo.MajorGenerators;
worksheet.Cells["B9"].Value = worksheet.Cells["B16"].Value = inputInfo.SitePortfolioType;
worksheet.Cells["B10"].Value = inputInfo.SiteRerating;
worksheet.Cells["B11"].Value = worksheet.Cells["B18"].Value = inputInfo.SiteDescription;
worksheet.Cells["B13"].Value = inputInfo.TADesrabilityNew;
worksheet.Cells["B14"].Value = inputInfo.MMTADescriptionNew;
worksheet.Cells["B15"].Value = inputInfo.MajorGeneratorsNew;
worksheet.Cells["B17"].Value = inputInfo.SiteRERatingFuture;
worksheet.Cells["B19"].Value = inputInfo.LeasedArea;
worksheet.Cells["B20"].Value = inputInfo.OperationSize;
worksheet.Cells["B21"].Value = inputInfo.Floors;
worksheet.Cells["B22"].Value = inputInfo.Seats;
worksheet.Cells["B23"].Value = inputInfo.BEType;
worksheet.Cells["B24"].Value = inputInfo.LeaseTenureAndTerm;
worksheet.Cells["B25"].Value = inputInfo.FreeRentalPeriod;
worksheet.Cells["B26"].Value = inputInfo.RentStructure;
worksheet.Cells["B27"].Value = inputInfo.MFStructure;
worksheet.Cells["B28"].Value = inputInfo.LeasedAreaNew;
worksheet.Cells["B29"].Value = inputInfo.OperationSizeNew;
worksheet.Cells["B30"].Value = inputInfo.FloorsNew;
worksheet.Cells["B31"].Value = inputInfo.SeatsNew;
worksheet.Cells["B32"].Value = inputInfo.BETypeNew;
worksheet.Cells["B33"].Value = inputInfo.LeaseTenureAndTermNew;
worksheet.Cells["B34"].Value = inputInfo.FreeRentalPeriodNew;
worksheet.Cells["B35"].Value = inputInfo.RentStructureNew;
worksheet.Cells["B36"].Value = inputInfo.MFStructureNew;
worksheet.Cells["B37"].Value = inputInfo.DRMFLastTY;
worksheet.Cells["B38"].Value = inputInfo.DRMFLastTYSales;
worksheet.Cells["B39"].Value = inputInfo.DRMF1stTY;
worksheet.Cells["B40"].Value = inputInfo.DRMF1stTYSales;
worksheet.Cells["B41"].Value = inputInfo.SRMFLastTY;
worksheet.Cells["B42"].Value = inputInfo.SRMFLastTYSales;
worksheet.Cells["B43"].Value = inputInfo.SRMF1stTY;
worksheet.Cells["B44"].Value = inputInfo.SRMF1stTYSales;
worksheet.Cells["B45"].Value = inputInfo.FairMarketRentAmount;
worksheet.Cells["B46"].Value = inputInfo.FairMarketRentAgent;
worksheet.Cells["B47"].Value = inputInfo.DR1stTYAmount;
worksheet.Cells["B48"].Value = inputInfo.AnnualSOILastTY;
worksheet.Cells["B49"].Value = inputInfo.AnnualSOIAvg;
worksheet.Cells["B50"].Value = inputInfo.CashROILastTY;
worksheet.Cells["B51"].Value = inputInfo.CashROIAvg;
worksheet.Cells["B52"].Value = inputInfo.LastRemodeling;
worksheet.Cells["B53"].Value = inputInfo.OriginalInvestment;
worksheet.Cells["B54"].Value = inputInfo.NBV;
worksheet.Cells["B55"].Value = inputInfo.AdditionalInvestmentCost;
worksheet.Cells["B56"].Value = inputInfo.ExclusivityClause;
worksheet.Cells["B57"].Value = inputInfo.McDsEarlyTerminationRight;
worksheet.Cells["B58"].Value = inputInfo.McDsEPRight;
worksheet.Cells["B59"].Value = inputInfo.LandlordEntity;
worksheet.Cells["B60"].Value = inputInfo.SpecialClauses;
worksheet.Cells["B61"].Value = inputInfo.OtherIssues;
worksheet.Cells["B62"].Value = inputInfo.ExclusivityClauseNew;
worksheet.Cells["B63"].Value = inputInfo.McDsEarlyTerminationRightNew;
worksheet.Cells["B64"].Value = inputInfo.McDsEPRightNew;
worksheet.Cells["B65"].Value = inputInfo.LandlordEntityNew;
worksheet.Cells["B66"].Value = inputInfo.SpecialClausesNew;
worksheet.Cells["B67"].Value = inputInfo.OtherIssuesNew;
worksheet.Cells["B68"].Value = inputInfo.DecisionLogic;
worksheet.Cells["B69"].Value = inputInfo.SpecialApprovalRequired;
}
public override void Parse(OfficeOpenXml.ExcelWorksheet worksheet, int currRow)
{
}
private void Import2()
{
if (!string.IsNullOrEmpty(_pathImport))
{
DataSet ds = null;
try
{
if (Path.GetExtension(_pathImport).Contains("xlsx")) // 2010
{
FileInfo newFile = new FileInfo(_pathImport);
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(newFile))
{
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets[1];
string sheetName = ws.Name;
ds = _db.ImportExcelToDS(_pathImport, sheetName);
}
}
else if (Path.GetExtension(_pathImport).Contains("csv"))
{
ds = _db.ImportCSVToDS(_pathImport);
}
else
{
ds = _db.ImportExcelToDS(_pathImport);
}
if (ds != null)
{
bool bColumnOk = true;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string colName = ds.Tables[0].Columns[j].ColumnName;
if (colName.Contains("#"))
{
ds.Tables[0].Columns[j].ColumnName = colName.Replace("#", ".");
}
if (!System.Text.RegularExpressions.Regex.IsMatch(ds.Tables[0].Columns[j].ColumnName, "^[\\sa-zA-Z0-9_\\.\\-%&/'()]*$"))
{
bColumnOk = false;
}
}
if (!bColumnOk)
{
MessageBox.Show(ResStrings.DbColumnForm_Importerror, ResStrings.DbColumnForm_toolStripButtonSubmit_Click_Updtate_Column_Info, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
_dt.Rows.Clear();
int nIndex = 0;
foreach (DataColumn colName in ds.Tables[0].Columns)
{
//remove sheet
_dt.Rows.Add(nIndex.ToString(), colName, colName, "STRING", "NONE", 1, 0);
nIndex++;
}
toolStripButtonSubmit.Enabled = true;
_bComeFromImport = true;
}
}
catch (IOException)
{
MessageBox.Show(ResStrings.DbColumnForm_Import_fileopen, ResStrings.strInfo, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
ErrorMessage.ExceptionMessageBox.Show(ex);
}
}
}
public ActionResult ExportToExcel(string Id)
{
if (string.IsNullOrWhiteSpace(Id))
{
return(null);
}
T_PurchaseOrder ObjItem = new T_PurchaseOrder()
{
Id = Guid.Parse(Id)
};
var vObjList = _blPurchaseOrder.GetPurchaseOrderDetailList(ObjItem);
// ExcelPackageF dll Need
OfficeOpenXml.ExcelPackage Ep = new OfficeOpenXml.ExcelPackage();
OfficeOpenXml.ExcelWorksheet workSheet = Ep.Workbook.Worksheets.Add("Report");
string sEndColumn = "M1";
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Font.Bold = true;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightSeaGreen);
var allCells = workSheet.Cells[string.Format("A1:{0}", sEndColumn)];
var cellFont = allCells.Style.Font;
workSheet.Cells["A1"].Value = "Slno";
//sheet["A3:A4"].Merge();
workSheet.Cells["B1"].Value = "Item";
workSheet.Cells["C1"].Value = "Brand";
workSheet.Cells["D1"].Value = "Guage";
workSheet.Cells["E1"].Value = "Quantity";
workSheet.Cells["F1"].Value = "UOM";
workSheet.Cells["G1"].Value = "Buying Rate";
workSheet.Cells["H1"].Value = "Buying Price";
workSheet.Cells["I1"].Value = "Selling Rate";
workSheet.Cells["J1"].Value = "Selling Price";
workSheet.Cells["K1"].Value = "Profit";
workSheet.Cells["L1"].Value = "Remark";
workSheet.Cells["M1"].Value = "Created Dated";
string dateformat = "dd-MM-yyyy";
int row = 2;
foreach (var item in vObjList)
{
//workSheet.Cells[string.Format("A{0}", row)].Value = item.EventDate;
//workSheet.Cells[string.Format("A{0}", row)].Style.Numberformat.Format = dateformat;
workSheet.Cells[string.Format("A{0}", row)].Value = item.SlNo;
workSheet.Cells[string.Format("B{0}", row)].Value = item.ItemsName;
workSheet.Cells[string.Format("C{0}", row)].Value = item.BrandName;
workSheet.Cells[string.Format("D{0}", row)].Value = item.GuageName;
workSheet.Cells[string.Format("E{0}", row)].Value = item.Quantity;
workSheet.Cells[string.Format("F{0}", row)].Value = item.UomName;
workSheet.Cells[string.Format("G{0}", row)].Value = item.BuyingRate;
workSheet.Cells[string.Format("H{0}", row)].Value = item.BuyingPrice;
workSheet.Cells[string.Format("I{0}", row)].Value = item.SellingRate;
workSheet.Cells[string.Format("J{0}", row)].Value = item.SellingPrice;
workSheet.Cells[string.Format("K{0}", row)].Value = item.DifferenceAmount;
workSheet.Cells[string.Format("L{0}", row)].Value = item.Remark;
workSheet.Cells[string.Format("M{0}", row)].Value = item.CreatedDate;
workSheet.Cells[string.Format("M{0}", row)].Style.Numberformat.Format = dateformat;
row++;
}
workSheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "Expense.csv" + DateTime.Now.ToString("s"));
Response.BinaryWrite(Ep.GetAsByteArray());
Response.End();
return(View());
}
public void Update(HttpPostedFileBase file, string username, string metadatafield, bool deleteData)
{
excelPackage = new OfficeOpenXml.ExcelPackage();
excelPackage.Load(file.InputStream);
workSheet = excelPackage.Workbook.Worksheets[1];
if (metadatafield == "AccessConstraints")
UpdateRestriction(username);
else if (metadatafield == "KeywordsNationalTheme")
UpdateTheme(username);
else if (metadatafield == "KeywordsNationalInitiative")
UpdateKeywordsNationalInitiative(username, deleteData);
else if (metadatafield == "KeywordsInspire")
UpdateKeywordsInspire(username, deleteData);
else if (metadatafield == "DistributionFormats")
UpdateDistributionFormats(username, deleteData);
else if (metadatafield == "ReferenceSystems")
UpdateReferenceSystems(username, deleteData);
excelPackage.Dispose();
}
/// <summary>
/// 创建表结构sheet
/// </summary>
/// <param name="epck"></param>
/// <param name="sheetName"></param>
/// <param name="tables"></param>
private static void CreateTableSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables)
{
OfficeOpenXml.ExcelWorksheet tbWorksheet = epck.Workbook.Worksheets.Add(sheetName);
int rowNum = 1, fromRow = 0, count = 0; // 行号计数器
// TODO 循环数据库表名
foreach (var table in tables)
{
// TODO 数据库名称
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Merge = true;
tbWorksheet.Cells[rowNum, 1].Value = table.TableName + " " + (!string.IsNullOrWhiteSpace(table.Comment) ? table.Comment : "");
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Bold = true;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Size = 16;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
// TODO 注意：保存起始行号
fromRow = rowNum;
rowNum++; // 行号+1
// tbWorksheet.Cells[int FromRow, int FromCol, int ToRow, int ToCol]
// TODO 列标题字体为粗体
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Bold = true;
// TODO 添加列标题
tbWorksheet.Cells[rowNum, 1].Value = "序号";
tbWorksheet.Cells[rowNum, 2].Value = "列名";
tbWorksheet.Cells[rowNum, 3].Value = "数据类型";
tbWorksheet.Cells[rowNum, 4].Value = "长度";
tbWorksheet.Cells[rowNum, 5].Value = "小数位";
tbWorksheet.Cells[rowNum, 6].Value = "主键";
tbWorksheet.Cells[rowNum, 7].Value = "自增";
tbWorksheet.Cells[rowNum, 8].Value = "允许空";
tbWorksheet.Cells[rowNum, 9].Value = "默认值";
tbWorksheet.Cells[rowNum, 10].Value = "列说明";
rowNum++; // 行号+1
// TODO 添加数据行,遍历数据库表字段
foreach (var column in table.Columns)
{
tbWorksheet.Cells[rowNum, 1].Value = column.ColumnOrder;
tbWorksheet.Cells[rowNum, 2].Value = column.ColumnName;
tbWorksheet.Cells[rowNum, 3].Value = column.ColumnTypeName;
tbWorksheet.Cells[rowNum, 4].Value = column.Length;
tbWorksheet.Cells[rowNum, 5].Value = column.Scale;
tbWorksheet.Cells[rowNum, 6].Value = column.IsPK;
tbWorksheet.Cells[rowNum, 7].Value = column.IsIdentity;
tbWorksheet.Cells[rowNum, 8].Value = column.CanNull;
tbWorksheet.Cells[rowNum, 9].Value = column.DefaultVal;
tbWorksheet.Cells[rowNum, 10].Value = column.Comment;
rowNum++; // 行号+1
}
// TODO 水平居中
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// TODO 垂直居中
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
// TODO 上下左右边框线
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
// TODO 处理空白行，分割用
if (count < tables.Count - 1)
{
//tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
//tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
tbWorksheet.Cells[rowNum, 1, rowNum, 10].Merge = true;
tbWorksheet.Cells[rowNum, 1, rowNum, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
tbWorksheet.Cells[rowNum, 1, rowNum, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DodgerBlue);
}
rowNum++; // 行号+1
count++; // 计数器+1
}
// TODO 设置表格样式
tbWorksheet.Cells.Style.WrapText = true; // 自动换行
tbWorksheet.Cells.Style.ShrinkToFit = true; // 单元格自动适应大小
}
public static void FontDefaults(ExcelWorksheet sheet)
{
sheet.Cells.Style.Font.Name = "Arial";
sheet.Cells.Style.Font.Size = 10;
}
public static void FontDefaults(ExcelWorksheet sheet)
{
}
