public HttpResponseMessage ExcelUpload()
{
string message = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
List <ExcelEntity> excelEntity = new List <ExcelEntity>();
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
return(result = Request.CreateResponse(HttpStatusCode.BadRequest, "This file format is not supported"));
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
for (int i = 0; i < finalRecords.Rows.Count; i++)
{
ExcelEntity objUser = new ExcelEntity();
objUser.Sno = Convert.ToString(finalRecords.Rows[i][0]);
objUser.Company = Convert.ToString(finalRecords.Rows[i][1]);
objUser.Sector = Convert.ToString(finalRecords.Rows[i][2]);
objUser.SubSector = Convert.ToString(finalRecords.Rows[i][3]);
objUser.Region = Convert.ToString(finalRecords.Rows[i][4]);
objUser.NoOfEmployee = Convert.ToString(finalRecords.Rows[i][5]);
objUser.TotalRevenu = Convert.ToString(finalRecords.Rows[i][6]);
objUser.Websites = Convert.ToString(finalRecords.Rows[i][7]);
excelEntity.Add(objUser);
}
result = Request.CreateResponse(HttpStatusCode.OK, excelEntity);
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
}
return(result);
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="patrolno"></param>
/// <returns></returns>
public Stream ExportExcel(string patrolno)
{
#region 查找数据
ResExportExcel response = new ResExportExcel();
bool success = false;
try
{
if (patrolno != null && patrolno != String.Empty && patrolno != "undefined")
{
ExcelEntity en = new ExcelEntity();
ResShowReport data = en.GetData(patrolno);//"PRN2017071800003");
//随机文件夹防止同时访问一个文件
string dirName = DateTime.Now.ToString("yyyyMMdd") + "\\" + data.patrol_header.report_uri;
string excelFileName = dirName + "\\" + CreateReportId();
response.excel_filename = excelFileName + CommonInfo.PDFExtend;
//设置成功状态
success = en.ExportExcel(data, dirName, excelFileName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
#region 日志输出
CommonInfo.Error("显示特巡报告书数据错误" + ex.Message);
#endregion
}
//返回消息体
if (success)
{
response.SetSuccess();
}
else
{
////默认是失败
//response.SetFailed();
}
//将消息序列化为Json格式数据
DataContractJsonSerializer obj2Json = new DataContractJsonSerializer(typeof(ResExportExcel));
MemoryStream ms = new MemoryStream();
obj2Json.WriteObject(ms, response);
//注意一定要设置流的位置到开始位置,否则没有消息输出
ms.Position = 0;
return(ms);
#endregion
}
public static ExcelEntity ReadExcelDetail(SpreadsheetDocument excel, List <string> sheetName, string file)
{
ExcelEntity ee = new ExcelEntity();
#region SheetStyle公用样式表
#region 主题色
ThemePart themPart = excel.WorkbookPart.ThemePart;
var themColor = ThemeColor.GetThemeColorList(themPart);
#endregion
//获取样式设置
Stylesheet styleSheet = excel.WorkbookPart.WorkbookStylesPart.Stylesheet;
#region 样式列表
CellFormats cellFormats = styleSheet.CellFormats;
List <CellFormatsList> cellFormatsList = new List <CellFormatsList>();
int index = 0;
foreach (CellFormat cell in cellFormats.ChildElements)
{
if (cell != null)
{
CellFormatsList cfl = new CellFormatsList();
cfl.styleIndex = index;
if (cell.NumberFormatId != null)
{
cfl.numFmtId = int.Parse(cell.NumberFormatId);
}
if (cell.FontId != null)
{
cfl.fontId = int.Parse(cell.FontId);
}
if (cell.FillId != null)
{
cfl.fillId = int.Parse(cell.FillId);
}
if (cell.BorderId != null)
{
cfl.borderId = int.Parse(cell.BorderId);
}
if (cell.ApplyAlignment != null)
{
cfl.applyAlignment = int.Parse(cell.ApplyAlignment);
}
if (cell.ApplyBorder != null)
{
cfl.applyBorder = int.Parse(cell.ApplyBorder);
}
if (cell.ApplyFont != null)
{
cfl.applyFont = int.Parse(cell.ApplyFont);
}
if (cell.ApplyNumberFormat != null)
{
cfl.applyNumberFormat = int.Parse(cell.ApplyNumberFormat);
}
if (cell.Alignment != null)
{
string ver = cell.Alignment.Vertical;
string hor = cell.Alignment.Horizontal;
if (!string.IsNullOrEmpty(ver))
{
if (ver == "center")
{
cfl.vertical = "htMiddle";
}
else
{
cfl.vertical = "ht" + ver.Substring(0, 1).ToUpper() + ver.Substring(1, ver.Length - 1);
}
}
else
{
cfl.vertical = "htBottom";
}
if (!string.IsNullOrEmpty(hor))
{
cfl.horizontal = "ht" + hor.Substring(0, 1).ToUpper() + hor.Substring(1, hor.Length - 1);
}
else
{
cfl.horizontal = "htLeft";
}
}
cellFormatsList.Add(cfl);
index++;
}
}
ee.CellFormatsList = cellFormatsList;
#endregion
#region 数据类型列表
NumberingFormats numberFormats = styleSheet.NumberingFormats;
List <NumFmtsList> numFmtList = new List <NumFmtsList>();
if (numberFormats != null)
{
foreach (NumberingFormat cell in numberFormats.ChildElements)
{
NumFmtsList nfl = new NumFmtsList();
if (cell.NumberFormatId != null)
{
nfl.numFmtId = (int)cell.NumberFormatId.Value;
}
if (cell.FormatCode != null)
{
nfl.formatCode = cell.FormatCode.Value;
}
numFmtList.Add(nfl);
}
}
ee.NumFmtsList = numFmtList;
#endregion
#region 字体样式
Fonts fonts = styleSheet.Fonts;
List <FontsList> fontsList = new List <FontsList>();
foreach (Font cell in fonts.ChildElements)
{
FontsList fl = new FontsList();
if (cell.FontSize != null)
{
fl.fontsize = cell.FontSize.Val + "px";
}
if (cell.FontName != null)
{
fl.fontname = cell.FontName.Val;
}
if (cell.Color != null)
{
if (cell.Color.Rgb != null && !string.IsNullOrEmpty(cell.Color.Rgb.ToString()))
{
fl.color = "#" + cell.Color.Rgb.ToString().Substring(2, 6);
}
}
if (cell.Italic != null)
{
fl.italic = "italic";
}
if (cell.Bold != null)
{
fl.bold = "bold";
}
if (cell.Underline != null)
{
fl.underline = "underline";
}
fontsList.Add(fl);
}
ee.FontsList = fontsList;
#endregion
#region 填充色样式
Fills fills = styleSheet.Fills;
List <FillsList> fillsList = new List <FillsList>();
foreach (Fill cell in fills.ChildElements)
{
FillsList fl = new FillsList();
if (cell.PatternFill != null)
{
fl.patternType = cell.PatternFill.PatternType;
if (cell.PatternFill.ForegroundColor != null)
{
if (cell.PatternFill.ForegroundColor.Rgb != null)
{
fl.fgColor = "#" + cell.PatternFill.ForegroundColor.Rgb.ToString().Substring(2, 6);
}
if (cell.PatternFill.ForegroundColor.Theme != null)
{
UInt32Value themeIndex = cell.PatternFill.ForegroundColor.Theme;
DoubleValue tint = cell.PatternFill.ForegroundColor.Tint;
if (tint != null)
{
var newColor = ThemeColor.ThemColorDeal(themeIndex, tint, themColor[themeIndex]);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
fl.fgColor = "#" + newColor.Name.Substring(2, 6);
}
else
{
fl.fgColor = "#" + themColor[themeIndex];
fl.fgColor = "#" + themColor[themeIndex];
}
}
}
}
fillsList.Add(fl);
}
ee.FillsList = fillsList;
#endregion
#region 边框样式
Borders borders = styleSheet.Borders;
List <BordersList> bordersList = new List <BordersList>();
var defaultBorderStyle = "1px solid #000";
foreach (Border cell in borders.ChildElements)
{
BordersList bl = new BordersList();
if (cell.LeftBorder != null)
{
if (cell.LeftBorder.Style != null)
{
bl.left = defaultBorderStyle;
}
}
if (cell.RightBorder != null)
{
if (cell.RightBorder.Style != null)
{
bl.right = defaultBorderStyle;
}
}
if (cell.TopBorder != null)
{
if (cell.TopBorder.Style != null)
{
bl.top = defaultBorderStyle;
}
}
if (cell.BottomBorder != null)
{
if (cell.BottomBorder.Style != null)
{
bl.bottom = defaultBorderStyle;
}
}
if (cell.DiagonalBorder != null)
{
if (cell.DiagonalBorder.Style != null)
{
bl.diagonal = cell.DiagonalBorder.Style;
}
}
bordersList.Add(bl);
}
ee.BordersList = bordersList;
#endregion
#endregion
List <SheetDataList> sheetDataList = new List <SheetDataList>();
List <PictureInfo> pictures = null;
for (int i = 0; i < sheetName.Count; i++)
{
SheetDataList sdl = new SheetDataList();
int RowCount = 0;
int ColumnCount = 0;
//得到工作表dimension
WorksheetPart worksheet = ExcelHelper.GetWorksheetPartByName(excel, sheetName[i]);
#region 获取单个Sheet表的数据
#region //批注
WorksheetCommentsPart comments = worksheet.WorksheetCommentsPart;
List <CommentCellsList> commentLists = new List <CommentCellsList>();
if (comments != null)
{
CommentList commentList = (CommentList)comments.Comments.ChildElements[1];
//批注列表
foreach (Comment comment in commentList.ChildElements)
{
CommentCellsList ccl = new CommentCellsList();
//坐标
var cell = GetCellXY(comment.Reference).Split('_');
var columnRow = int.Parse(cell[0].ToString()) - 1;
var columnCol = GetColumnIndex(cell[1]);
//批注内容
var commentVal = comment.InnerText;
ccl.row = columnRow;
ccl.col = columnCol;
ccl.comment = comment.InnerText;
//var commentCell = "{\"Row\":\""+ columnRow + "\",\"Col\":\"" + columnCol + ",\"Comment\":\"" + commentVal + "\"}";
commentLists.Add(ccl);
}
}
sdl.Comments = commentLists;
#endregion
#region //获取合并单元格
IEnumerable <MergeCells> mergeCells = worksheet.Worksheet.Elements <MergeCells>();
List <MergeCellsList> mergeCellList = new List <MergeCellsList>();
if (mergeCells.Count() > 0)
{
for (int k = 0; k < mergeCells.First().ChildElements.Count; k++)
{
MergeCell mergeCell = (MergeCell)mergeCells.First().ChildElements[k];
var reference = mergeCell.Reference.ToString().Split(':');
var startCell = GetCellXY(reference[0]).Split('_');
var endCell = GetCellXY(reference[1]).Split('_');
MergeCellsList mcl = new MergeCellsList();
mcl.row = int.Parse(startCell[0]) - 1;
mcl.rowspan = int.Parse(endCell[0]) - int.Parse(startCell[0]) + 1;
mcl.col = GetColumnIndex(startCell[1]);
mcl.colspan = GetColumnIndex(endCell[1]) - mcl.col + 1;
//mcl.reference = mergeCell.Reference.ToString();
mergeCellList.Add(mcl);
}
}
sdl.MergeCells = mergeCellList;
#endregion
//获取超链接列表
//var hyperlinks = worksheet.RootElement.Descendants<Hyperlinks>().First().Cast<Hyperlink>();
#region //读取图片
DrawingsPart drawingPart = worksheet.GetPartsOfType <DrawingsPart>().ToList().FirstOrDefault();
pictures = new List <PictureInfo>();
if (drawingPart != null)
{
int tempIndex = 1;
foreach (var part in drawingPart.Parts)
{
PictureInfo pic = new PictureInfo();
ImagePart imgPart = (ImagePart)part.OpenXmlPart;
System.Drawing.Image img1 = System.Drawing.Image.FromStream(imgPart.GetStream());
var newFilename = Guid.NewGuid().ToString("N") + ".png";
string[] sArray = Regex.Split(file, "UserFile", RegexOptions.IgnoreCase);
string newFilePath = sArray[0] + "_Temp\\" + newFilename;
img1.Save(newFilePath);
//pic.Image = img1;
pic.RefId = part.RelationshipId;//"rId" + imgPart.Uri.ToString().Split('/')[3].Split('.')[0].Substring(5);
pic.ImageUrl = newFilePath;
pic.ImageName = newFilename;
pic.ImgHeight = img1.Height;
pic.ImgWidth = img1.Width;
pictures.Add(pic);
tempIndex++;
}
//获取图片定位
var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any
(a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList();
foreach (var worksheetDrawing in worksheetDrawings)
{
if (worksheetDrawing.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor")
{
TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef =
(DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture)
anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName ==
"DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture");
if (picDef != null)
{
var embed = picDef.BlipFill.Blip.Embed;
if (embed != null)
{
var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText);
picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText);
picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText);
}
}
// anchor.FromMarker.RowId + anchor.FromMarker.ColumnId
}
}
}
sdl.PictureList = pictures;
#endregion
#region 读取表格数据
List <SheetDatas> sheetDatas = new List <SheetDatas>();
if (worksheet.Rows().Count() > 0)
{
RowCount = int.Parse((worksheet.Rows().Last()).RowId);
}
int TempColumn = 0;
foreach (OpenXmlPowerTools.Row row in worksheet.Rows())
{
foreach (OpenXmlPowerTools.Cell cell in row.Cells())
{
#region 读取超链接
//var hyperlink = hyperlinks.SingleOrDefault(c => c.Reference.Value == cell.Column);
//if (hyperlink != null)
//{
// var hyperlinksRelation = worksheet.HyperlinkRelationships.SingleOrDefault(c => c.Id == hyperlink.Id);
// if (hyperlinksRelation != null)
// {
// //这是最终我们需要的超链接
// var url = hyperlinksRelation.Uri.ToString();
// }
//}
#endregion
TempColumn = (row.Cells().Last()).ColumnIndex;
if (ColumnCount < TempColumn)
{
ColumnCount = TempColumn + 1;
}
SheetDatas sheetData = new SheetDatas();
sheetData.RowId = int.Parse(row.RowId);
sheetData.ColumnId = cell.ColumnIndex;
sheetData.Column = cell.Column;
sheetData.Type = cell.Type;
sheetData.Value = cell.Value;
sheetData.SharedString = cell.SharedString;
sheetData.Formula = cell.Formula;
sheetData.StyleId = cell.Style;
#region 样式赋值
if (sheetData.StyleId != null)
{
CellFormatsList cfl = cellFormatsList[(int)sheetData.StyleId];
sheetData.FontName = fontsList[cfl.fontId].fontname;
sheetData.FontSize = fontsList[cfl.fontId].fontsize;
sheetData.FontColor = fontsList[cfl.fontId].color;
sheetData.FontBold = fontsList[cfl.fontId].bold;
sheetData.Italic = fontsList[cfl.fontId].italic;
sheetData.Underline = fontsList[cfl.fontId].underline;
sheetData.AligmentVertical = cfl.vertical;
sheetData.AligmentHorizontal = cfl.horizontal;
sheetData.FillType = fillsList[cfl.fillId].patternType;
sheetData.FillForegroundColor = fillsList[cfl.fillId].fgColor;
sheetData.FillBackgroundColor = fillsList[cfl.fillId].bgColor;
sheetData.LeftBorder = bordersList[cfl.borderId].left;
sheetData.RightBorder = bordersList[cfl.borderId].right;
sheetData.TopBorder = bordersList[cfl.borderId].top;
sheetData.BottomBorder = bordersList[cfl.borderId].bottom;
sheetData.DiagonalBorder = bordersList[cfl.borderId].diagonal;
}
#endregion
if (cell.Style != null)
{
var cellf = cellFormatsList[(int)cell.Style];
if (cellf.applyNumberFormat > 0 && cell.Type == null && cell.Value != null)
{
//for (int n = 0; n < numFmtList.Count; n++)
//{
// if (numFmtList[n].numFmtId == cellf.numFmtId|| cellf.numFmtId == 14)
// {
// sheetData.Type = "s";
// sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
// break;
// }
//}
if (cellf.numFmtId == 58)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToString("M月d日");
}
else if (cellf.numFmtId == 14)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
}
else
{
for (int n = 0; n < numFmtList.Count; n++)
{
if (numFmtList[n].numFmtId == cellf.numFmtId)
{
sheetData.Type = "s";
sheetData.SharedString = DateTime.FromOADate(double.Parse(cell.Value)).ToShortDateString();
break;
}
}
}
}
else
{
sheetData.Value = cell.Value;
}
}
sheetDatas.Add(sheetData);
}
}
if (ColumnCount < 5)
{
ColumnCount = 5;
}
if (RowCount < 20)
{
RowCount = 20;
}
sdl.SheetData = sheetDatas;
#endregion
sdl.SheetName = sheetName[i];
sdl.SheetId = "sheet" + (i + 1);
sdl.TotalRow = (RowCount + 1);
sdl.TotalColumn = ColumnCount;
sheetDataList.Add(sdl);
//htmlStr = EMW.API.Serializer.ObjectToString(sheetDataList);//
//htmlStr = "{\"SheetData\":" + htmlStr + ",\"Comments\":" + EMW.API.Serializer.ObjectToString(commentLists) + ",\"MergeCells\":" + EMW.API.Serializer.ObjectToString(mergeCellList) + ",\"TotalRow\":" + (RowCount + 1) + ",\"TotalColumn\":" + ColumnCount + ",\"SheetName\":\"" + sheetName[i] + "\"}";
//htmlSheet.Add(htmlStr);
#endregion
}
ee.SheetDataList = sheetDataList;
return(ee);
}