List<OffLineRecord> items = new List<OffLineRecord>();//数据源
//生成Excel
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
ISheet sheet2 = workbook.CreateSheet("Sheet2");
ISheet sheet3 = workbook.CreateSheet("Sheet3");
IRow row0 = sheet1.CreateRow(0);
ICell cell0 = row0.CreateCell(0);
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.Alignment = HorizontalAlignment.Center;
cellStyle1.VerticalAlignment = VerticalAlignment.Center;
cell0.CellStyle = cellStyle1;
IFont font1 = (HSSFFont)workbook.CreateFont();
font1.FontHeightInPoints = 48;
HSSFRichTextString richTextPlateNo = new HSSFRichTextString("离线车辆数据");
richTextPlateNo.ApplyFont(font1);
cell0.SetCellValue(richTextPlateNo + " 开始时间:" + beginDate.ToString("yyyy-MM-dd") + " 结束时间:" + endDate.ToString("yyyy-MM-dd"));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
IRow header_row = sheet1.CreateRow(1);
ICell header_cell0 = header_row.CreateCell(0);
header_cell0.SetCellValue("车牌号");
ICell header_cell1 = header_row.CreateCell(1);
header_cell1.SetCellValue("离线时间");
ICell header_cell2 = header_row.CreateCell(2);
header_cell2.SetCellValue("离线天数");
ICell header_cell3 = header_row.CreateCell(3);
header_cell3.SetCellValue("最后位置");
if (items != null)
{
for (int i = 0; i < items.Count; i++)
{
IRow row = sheet1.CreateRow(i + 2);
ICell row_cell0 = row.CreateCell(0);
long obdid = Convert.ToInt64(items[i]._str_obdid);
string plateNumber = new OBDWebContext().Vehicles.Where(v => v.OBDID == obdid).FirstOrDefault().PlateNumber;
row_cell0.SetCellValue(plateNumber);
ICell row_cell1 = row.CreateCell(1);
row_cell1.SetCellValue(items[i]._offlinedate.ToString("yyyy-MM-dd HH:mm:ss"));
ICell row_cell2 = row.CreateCell(2);
row_cell2.SetCellValue(items[i]._diffdays);
ICell row_cell3 = row.CreateCell(3);
WebClient wc = new WebClient();
wc.Headers.Add("Content-Type", "application/json");
byte[] resData = wc.DownloadData(string.Format("http://api.map.baidu.com/geocoder?location={0},{1}&output=json", items[i]._baidulat, items[i]._baidulng));
string resString = System.Text.Encoding.UTF8.GetString(resData);
GeocoderAddr geocoderAddr = Newtonsoft.Json.JsonConvert.DeserializeObject<GeocoderAddr>(resString);
string addr = geocoderAddr.result.formatted_address;
row_cell3.SetCellValue(addr);
}
}
sheet1.SetColumnWidth(0, 5000);
sheet1.SetColumnWidth(1, 5000);
sheet1.SetColumnWidth(2, 5000);
sheet1.SetColumnWidth(3, 5000);
sheet1.ForceFormulaRecalculation = true;
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "离线车辆数据.xls");
//生成Excel
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
ISheet sheet2 = workbook.CreateSheet("Sheet2");
ISheet sheet3 = workbook.CreateSheet("Sheet3");
IRow row0 = sheet1.CreateRow(0);
ICell cell0 = row0.CreateCell(0);
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.Alignment = HorizontalAlignment.Center;
cellStyle1.VerticalAlignment = VerticalAlignment.Center;
cell0.CellStyle = cellStyle1;
IFont font1 = (HSSFFont)workbook.CreateFont();
font1.FontHeightInPoints = 48;
HSSFRichTextString richTextPlateNo = new HSSFRichTextString("离线车辆数据");
richTextPlateNo.ApplyFont(font1);
cell0.SetCellValue(richTextPlateNo + " 开始时间:" + beginDate.ToString("yyyy-MM-dd") + " 结束时间:" + endDate.ToString("yyyy-MM-dd"));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
IRow header_row = sheet1.CreateRow(1);
ICell header_cell0 = header_row.CreateCell(0);
header_cell0.SetCellValue("车牌号");
ICell header_cell1 = header_row.CreateCell(1);
header_cell1.SetCellValue("离线时间");
ICell header_cell2 = header_row.CreateCell(2);
header_cell2.SetCellValue("离线天数");
ICell header_cell3 = header_row.CreateCell(3);
header_cell3.SetCellValue("最后位置");
if (items != null)
{
for (int i = 0; i < items.Count; i++)
{
IRow row = sheet1.CreateRow(i + 2);
ICell row_cell0 = row.CreateCell(0);
long obdid = Convert.ToInt64(items[i]._str_obdid);
string plateNumber = new OBDWebContext().Vehicles.Where(v => v.OBDID == obdid).FirstOrDefault().PlateNumber;
row_cell0.SetCellValue(plateNumber);
ICell row_cell1 = row.CreateCell(1);
row_cell1.SetCellValue(items[i]._offlinedate.ToString("yyyy-MM-dd HH:mm:ss"));
ICell row_cell2 = row.CreateCell(2);
row_cell2.SetCellValue(items[i]._diffdays);
ICell row_cell3 = row.CreateCell(3);
WebClient wc = new WebClient();
wc.Headers.Add("Content-Type", "application/json");
byte[] resData = wc.DownloadData(string.Format("http://api.map.baidu.com/geocoder?location={0},{1}&output=json", items[i]._baidulat, items[i]._baidulng));
string resString = System.Text.Encoding.UTF8.GetString(resData);
GeocoderAddr geocoderAddr = Newtonsoft.Json.JsonConvert.DeserializeObject<GeocoderAddr>(resString);
string addr = geocoderAddr.result.formatted_address;
row_cell3.SetCellValue(addr);
}
}
sheet1.SetColumnWidth(0, 5000);
sheet1.SetColumnWidth(1, 5000);
sheet1.SetColumnWidth(2, 5000);
sheet1.SetColumnWidth(3, 5000);
sheet1.ForceFormulaRecalculation = true;
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "离线车辆数据.xls");