///
/// datatable 导出
///
///
///
public byte[] GetExcelByte(DataTable dt, string err)
{
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("候车亭批量导入");
workSheet.Cells[1, 1].Value = "媒体类型 *";
workSheet.Cells[1, 2].Value = "类型子类 *";
workSheet.Cells[1, 3].Value = "媒体位置 *";
for (int i = 1; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
workSheet.Cells[i + 1, 1].Value = dr[0];
workSheet.Cells[i + 1, 2].Value = dr[1];
workSheet.Cells[i + 1, 3].Value = dr[2];
}
return package.GetAsByteArray();
}
}
///
/// execl 转成 table
///
///
///
public DataTable ExcelToTable(string path)
{
DataTable vTable = new DataTable();
FileInfo existingFile = new FileInfo(path);
try
{
FileInfo file = new FileInfo(path);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int vSheetCount = package.Workbook.Worksheets.Count;
// 获取总 Sheet 页
int maxColumnNum = worksheet.Dimension.End.Column;// 最大列
int minColumnNum = worksheet.Dimension.Start.Column;// 最小列
int maxRowNum = worksheet.Dimension.End.Row;// 最小行
int minRowNum = worksheet.Dimension.Start.Row;// 最大行
DataColumn vC;
for (int j = 1; j <= maxColumnNum; j++)
{
vC = new DataColumn("A_" + j, typeof(string));
vTable.Columns.Add(vC);
}
for (int n = 1; n <= maxRowNum; n++)
{
DataRow vRow = vTable.NewRow();
for (int m = 1; m <= maxColumnNum; m++)
{
vRow[m - 1] = worksheet.Cells[n, m].Value;
}
vTable.Rows.Add(vRow);
}
}
}
catch (Exception vErr)
{
Console.WriteLine(vErr.Message);
}
return vTable;
}
///
/// 把二进制流转成文件
///
/// 二进制流,类似 (data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,) 开头的字符串
/// 文件扩展名
///
public string UploadExecl(string path, string extension)
{
string sPath = host.ContentRootPath + "\\BatchUpload";// 保存的路径
if (!Directory.Exists(sPath))
{
Directory.CreateDirectory(sPath);
}
var regex = new Regex(@"data:(?
var match = regex.Match(path);
var mimeType = match.Groups["mime"].Value;
var encodingCode = match.Groups["encoding"].Value;
var data = match.Groups["data"].Value;
byte[] targetFileByte = Convert.FromBase64String(data);
string[] mimeExtension = mimeType.Split('/');
string fileExtension = extension;
Random random = new Random();
// 文件保存
string fileName = string.Format("{0:yyyyMMddHHmmss}{1}", DateTime.Now, random.Next());
string filePath = string.Format("{0}\\{1}.{2}", sPath, fileName, fileExtension);
FileStream file = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write);
file.Write(targetFileByte,0, targetFileByte.Length);
file.Close();
return filePath;
}
来源: http://www.bubuko.com/infodetail-2441378.html