public void WriteNorthwindDataSetToXls()
{
string northwindXls = "Northwind.xls";
using (DataSet dataSet = GetNorthwindDataSet())
{
XlsDocument xls = new XlsDocument(dataSet);
SetTestMetadata(xls); //so metadata doesn't differ on different test machines and kill the compare
Assert.AreEqual(NORTHWIND_TABLE_NAMES.Length, xls.Workbook.Worksheets.Count, "Worksheet count");
xls.Save(true);
AssertFilesBinaryEqual(Path.Combine(TestsConfig.ReferenceFileFolder, northwindXls), northwindXls);
}
}
public void ApplyCustomFormat()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, 1.13);
Assert.AreEqual(1, doc.Workbook.Formats.Count, "Format count before applying new format");
cell.Format = "\"x\"#,##0.00_);(\"x\"#,##0.00)";
Assert.AreEqual(2, doc.Workbook.Formats.Count, "Format count after applying new format");
doc.FileName = "ApplyCustomFormat";
doc.Save(true);
string file = Environment.CurrentDirectory;
if (!file.EndsWith("\\"))
file += "\\";
file += doc.FileName;
AssertPropertyViaExcelOle(file, CellProperties.Text, "x1.13 ", "Cell Text");
}
static void Main(string[] args)
{
// Download Image File OK.
/* */
Console.WriteLine(Environment.CurrentDirectory);
Console.WriteLine(AppDomain.CurrentDomain.BaseDirectory);
Console.WriteLine(Directory.GetCurrentDirectory());
Console.WriteLine();
XlsDocument doc = new XlsDocument();
Worksheet s = doc.Workbook.Worksheets.Add("Review");
Row row = s.Rows.AddRow(1);
Cell c = s.Cells.Add(1, 1, "id");
s.Cells.Add(5, 5, "content");
doc.Save(true);
//Console.ReadKey();
}
public void Save()
{
string fileName = "TestSave.xls";
XlsDocument doc = new XlsDocument();
doc.FileName = fileName;
string path = Path.Combine(Environment.CurrentDirectory, fileName);
if (File.Exists(path))
{
File.Delete(path);
}
Assert.IsFalse(File.Exists(path));
doc.Save();
Assert.IsTrue(File.Exists(path));
File.Delete(path);
}
public void SaveNoOverwrite()
{
string fileName = "TestSave.xls";
XlsDocument doc = new XlsDocument();
doc.FileName = fileName;
string path = Path.Combine(Environment.CurrentDirectory, fileName);
File.WriteAllText(path, "test");
Assert.IsTrue(File.Exists(path));
try
{
doc.Save();
}
catch (IOException ex)
{
StringAssert.Contains("already exists", ex.Message);
throw;
}
}
public void ApplyCustomFormat()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, 1.13);
Assert.AreEqual(1, doc.Workbook.Formats.Count, "Format count before applying new format");
cell.Format = "\"x\"#,##0.00_);(\"x\"#,##0.00)";
Assert.AreEqual(2, doc.Workbook.Formats.Count, "Format count after applying new format");
doc.FileName = "ApplyCustomFormat";
doc.Save(true);
string file = Environment.CurrentDirectory;
if (!file.EndsWith("\\"))
{
file += "\\";
}
file += doc.FileName;
AssertPropertyViaExcelOle(file, CellProperties.Text, "x1.13 ", "Cell Text");
}
public static string WriteDocument(XlsDocumentDelegate docDelegate)
{
string path = Environment.CurrentDirectory;
if (!path.EndsWith("\\"))
{
path += "\\";
}
string fileName = "writedocument";
XlsDocument xls = new XlsDocument();
xls.FileName = fileName;
if (docDelegate != null)
{
docDelegate(xls);
}
xls.Save(path, true);
return(string.Format("{0}{1}.xls", path, fileName));
}
//static public void ExportDataGrid(DataTable dt, string FileType, string FileName) //从DataGrid导出
//{
// DataGrid dg = new DataGrid();
// //DataSet dg = new DataSet();
// dg.DataSource = dt;
// dg.DataBind();
// //定义文档类型、字符编码
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
// HttpContext.Current.Response.Charset = "UTF-8";
// HttpContext.Current.Response.ContentEncoding = Encoding.Default;
// HttpContext.Current.Response.ContentType = FileType;
// dg.EnableViewState = false;
// //定义一个输入流
// StringWriter tw = new StringWriter();
// HtmlTextWriter hw = new HtmlTextWriter(tw);
// //目标数据绑定到输入流输出
// dg.RenderControl(hw);
// HttpContext.Current.Response.Write(tw.ToString());
// HttpContext.Current.Response.End();
//}
static public void Output(DataTable dt, string type, string name)
{
org.in2bits.MyXls.XlsDocument doc = new org.in2bits.MyXls.XlsDocument();
doc.FileName = DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();//excel文件名称
//org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");//Excel工作表名称
org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");
org.in2bits.MyXls.Cells cells = sheet.Cells;
int colnum = dt.Columns.Count;//获取DataTable列数
for (int i = 0; i < colnum; i++)
{
cells.Add(1, (i + 1), dt.Columns[i].Caption.ToString());//导出DataTable列名
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < colnum; j++)
{
cells.Add((i + 2), (j + 1), dt.Rows[i][j].ToString());
}
}
doc.Save(HttpContext.Current.Server.MapPath("file/"));
string strFilePath = HttpContext.Current.Server.MapPath("file/") + doc.FileName;
FileInfo fi = new FileInfo(strFilePath);//excelFile为文件在服务器上的地址
HttpResponse contextResponse = HttpContext.Current.Response;
contextResponse.Clear();
contextResponse.Buffer = true;
contextResponse.Charset = "utf8"; //设置了类型为中文防止乱码的出现
contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", name)); //定义输出文件和文件名
contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
contextResponse.ContentEncoding = Encoding.Default;
contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
contextResponse.WriteFile(fi.FullName);
contextResponse.Flush();
contextResponse.End();
}
public static void OutBidGysExcel(string strid, HttpResponseBase rs)
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "报价供应商信息表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("报价供应商");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 7;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 4; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "序号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "供应商名称", cellXF); break;
case 3: cell = cells.Add(1, i, "报价（包括费用）", cellXF); break;
case 4: cell = cells.Add(1, i, "备注", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
var bid = 网上竞标管理.查找网上竞标(long.Parse(strid));
var bjgys = bid.报价供应商列表;
for (int m = 0; m < bjgys.Count(); m++)
{
for (int n = 1; n <= 4; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, m + 1, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, bjgys[m].报价供应商.用户数据.企业基本信息.企业名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, bjgys[m].总价, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, bjgys[m].备注, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
for (int i = 1; i <= 4; i++)
{
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
case 2: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
case 3: cell = cells.Add(bjgys.Count + 2, i, "参与人员签字：", cellXF1); break;
case 4: cell = cells.Add(bjgys.Count + 2, i, "", cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static string WriteDocument(XlsDocumentDelegate docDelegate)
{
string path = Environment.CurrentDirectory;
if (!path.EndsWith("\\"))
path += "\\";
string fileName = "writedocument";
XlsDocument xls = new XlsDocument();
xls.FileName = fileName;
if (docDelegate != null)
docDelegate(xls);
xls.Save(path, true);
return string.Format("{0}{1}.xls", path, fileName);
}
private static void trySaveXlsFile(XlsDocument xls, string saveFileName)
{
xls.FileName = saveFileName;
try
{
xls.Save(true);
}
catch (Exception ex)
{
throw new Exception(string.Format("无法保存Excel文件：{0}", ex.Message)
, ex);
}
}
public static void ToExcel(List<DataModel> dm,out string Msg)
{
Msg = "导入成功";
if(dm.Count<=0)
{
Msg="没有相应的数据";
return;
}
//行数不可以大于65536
if (dm.Count > 65536)
{
Msg="数据量太大，请减少时间区间进行查询";
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "Save Excel File";
sfd.Filter = "Excel File(*.xls)|*.xls";
//sfd.FilterIndex = 1;
sfd.OverwritePrompt = true;
sfd.DefaultExt = "xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
string FileName = sfd.FileName;
XlsDocument xls = new XlsDocument();//创建空xls文档
xls.FileName = FileName;
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("Report"); //创建一个工作页为Report
////设定列宽度--Carton Number、Class、Module Number
//ColumnInfo colInfo0 = new ColumnInfo(xls, sheet);
//colInfo0.ColumnIndexStart = 0;
//colInfo0.ColumnIndexEnd = 0;
//colInfo0.Width = 20 * 256;
//sheet.AddColumnInfo(colInfo0);
//ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
//colInfo1.ColumnIndexStart = 1;
//colInfo1.ColumnIndexEnd = 1;
//colInfo1.Width = 15 * 256;
//sheet.AddColumnInfo(colInfo1);
//ColumnInfo colInfo2 = new ColumnInfo(xls, sheet);
//colInfo2.ColumnIndexStart = 2;
//colInfo2.ColumnIndexEnd = 2;
//colInfo2.Width = 18 * 256;
//sheet.AddColumnInfo(colInfo2);
//ColumnInfo colInfo3 = new ColumnInfo(xls, sheet);
//colInfo3.ColumnIndexStart = 3;
//colInfo3.ColumnIndexEnd = 3;
//colInfo3.Width = 10 * 256;
//sheet.AddColumnInfo(colInfo3);
//ColumnInfo colInfo8 = new ColumnInfo(xls, sheet);
//colInfo8.ColumnIndexStart = 8;
//colInfo8.ColumnIndexEnd = 8;
//colInfo8.Width = 12 * 256;
//sheet.AddColumnInfo(colInfo8);
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//创建表头
Cell title = cells.Add(1, 1, "车间");
title.HorizontalAlignment = HorizontalAlignments.Centered;
title.VerticalAlignment = VerticalAlignments.Centered;
Cell title1 = cells.Add(1, 2, "线别");
title1.HorizontalAlignment = HorizontalAlignments.Centered;
title1.VerticalAlignment = VerticalAlignments.Centered;
Cell title2 = cells.Add(1, 3, "设备名称");
title2.HorizontalAlignment = HorizontalAlignments.Centered;
title2.VerticalAlignment = VerticalAlignments.Centered;
Cell title3 = cells.Add(1, 4, "组件号");
title3.HorizontalAlignment = HorizontalAlignments.Centered;
title3.VerticalAlignment = VerticalAlignments.Centered;
Cell title4 = cells.Add(1, 5, "刷入时间");
title4.HorizontalAlignment = HorizontalAlignments.Centered;
title4.VerticalAlignment = VerticalAlignments.Centered;
int m=0;
double dtmp=0.00;
foreach(DataModel dataModel in dm)
{
m++;
Cell value = cells.Add(m + 1, 1, dataModel.WORKSHOP);
Cell value1 = cells.Add(m + 1, 2, dataModel.LINE);
Cell value2 = cells.Add(m + 1, 3, dataModel.EQUIPMENT_NAME);
Cell value3 = cells.Add(m + 1, 4, dataModel.MODULE_SN);
Cell value4 = cells.Add(m + 1, 5, dataModel.CREATED_ON);
value.HorizontalAlignment = HorizontalAlignments.Centered;
value.VerticalAlignment = VerticalAlignments.Centered;
value1.HorizontalAlignment = HorizontalAlignments.Centered;
value1.VerticalAlignment = VerticalAlignments.Centered;
value2.HorizontalAlignment = HorizontalAlignments.Centered;
value2.VerticalAlignment = VerticalAlignments.Centered;
value3.HorizontalAlignment = HorizontalAlignments.Centered;
value3.VerticalAlignment = VerticalAlignments.Centered;
value4.HorizontalAlignment = HorizontalAlignments.Centered;
value4.VerticalAlignment = VerticalAlignments.Centered;
}
try
{
xls.Save(true);
Msg = "导入成功";
}
catch(Exception e)
{
Msg="导入失败"+e.Message;
}
}
}
public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
{
XlsDocument xls = new XlsDocument();
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff", System.Globalization.DateTimeFormatInfo.InvariantInfo);
xls.SummaryInformation.Author = "yongfa365"; //填加xls文件作者信息
xls.SummaryInformation.NameOfCreatingApplication = "liu yongfa"; //填加xls文件创建程序信息
xls.SummaryInformation.LastSavedBy = "LastSavedBy"; //填加xls文件最后保存者信息
xls.SummaryInformation.Comments = "Comments"; //填加xls文件作者信息
xls.SummaryInformation.Title = "title"; //填加xls文件标题信息
xls.SummaryInformation.Subject = "Subject";//填加文件主题信息
xls.DocumentSummaryInformation.Company = "company";//填加文件公司信息
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");//状态栏标题名称
Cells cells = sheet.Cells;
foreach (DataColumn col in dtSource.Columns)
{
Cell cell = cells.Add(1, col.Ordinal + 1, col.ColumnName);
cell.Font.FontFamily = FontFamilies.Roman; //字体
cell.Font.Bold = true; //字体为粗体
}
#region 填充内容
XF dateStyle = xls.NewXF();
dateStyle.Format = "yyyy-mm-dd";
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int rowIndex = i + 2;
int colIndex = j + 1;
string drValue = dtSource.Rows[i][j].ToString();
switch (dtSource.Rows[i][j].GetType().ToString())
{
case "System.String"://字符串类型
cells.Add(rowIndex, colIndex, drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cells.Add(rowIndex, colIndex, dateV, dateStyle);
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cells.Add(rowIndex, colIndex, boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cells.Add(rowIndex, colIndex, intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cells.Add(rowIndex, colIndex, doubV);
break;
case "System.DBNull"://空值处理
cells.Add(rowIndex, colIndex, null);
break;
default:
cells.Add(rowIndex, colIndex, null);
break;
}
}
}
#endregion
//foreach (DataRow row in dtSource.Rows)
//{
// rowIndex++;
// colIndex = 0;
// foreach (DataColumn col in dtSource.Columns)
// {
// colIndex++;
// cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString());//全都当文本型处理
// }
//}
xls.FileName = strFileName;
xls.Save();
}
/// <summary>
/// MyXls简单Demo，快速入门代码
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <remarks>MyXls认为Excel的第一个单元格是：(1,1)</remarks>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
XlsDocument xls = new XlsDocument();
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
//填充表头
foreach (DataColumn col in dtSource.Columns)
{
sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
}
//填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, dtSource.Rows[i][j].ToString());
}
}
//保存
xls.FileName = strFileName;
xls.Save();
}
public static void PutDemandInfo(string strid, HttpResponseBase rs)
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "军队物资集中采购需求计划表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("需求采购任务表");
Worksheet sheet1 = xls.Workbook.Worksheets.Add("需求采购任务分发物资列表");
Worksheet sheet2 = xls.Workbook.Worksheets.Add("需求采购任务分发列表");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
ColumnInfo cinfo1 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo2 = new ColumnInfo(xls, sheet2);
ColumnInfo cinfo3 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo4 = new ColumnInfo(xls, sheet1);
ColumnInfo cinfo5 = new ColumnInfo(xls, sheet1);
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 10;//列结束
cinfo.Width = 100 * 40;//列宽度
sheet.AddColumnInfo(cinfo);
cinfo1.ColumnIndexStart = 0;//列开始
cinfo1.ColumnIndexEnd = 10;//列结束
cinfo1.Width = 100 * 40;//列宽度
sheet1.AddColumnInfo(cinfo1);
cinfo2.ColumnIndexStart = 0;//列开始
cinfo2.ColumnIndexEnd = 10;//列结束
cinfo2.Width = 100 * 40;//列宽度
sheet2.AddColumnInfo(cinfo2);
cinfo3.ColumnIndexStart = 0;//列开始
cinfo3.ColumnIndexEnd = 0;//列结束
cinfo3.Width = 10000;//列宽度
cinfo4.ColumnIndexStart = 6;//列开始
cinfo4.ColumnIndexEnd = 6;//列结束
cinfo4.Width = 2800;//列宽度
cinfo5.ColumnIndexStart = 8;//列开始
cinfo5.ColumnIndexEnd = 8;//列结束
cinfo5.Width = 2800;//列宽度
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.Font.Height = 20 * 12;
cellXF.Font.Weight = FontWeight.SemiBold;
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Font.Height = 20 * 10;
cellXF1.Font.Weight = FontWeight.Normal;
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
cells.Merge(1, 1, 1, 10);
Cell header = cells.Add(1, 1, "需求采购任务");
header.Font.Height = 250;
header.Font.Weight = FontWeight.Bold;
header.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(2, i, "管理部门", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(2, i, "包含物资项", cellXF); break;
case 3: cell = cells.Add(2, i, "包含分发项", cellXF); break;
case 4: cell = cells.Add(2, i, "采购机构", cellXF); break;
case 5: cell = cells.Add(2, i, "采购方式建议", cellXF); break;
case 6: cell = cells.Add(2, i, "下达时间", cellXF); break;
case 7: cell = cells.Add(2, i, "完成时间", cellXF); break;
case 8: cell = cells.Add(2, i, "联系人", cellXF); break;
case 9: cell = cells.Add(2, i, "联系电话", cellXF); break;
case 10: cell = cells.Add(2, i, "描述", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
需求采购任务 plan = 需求采购任务管理.查找需求采购任务(long.Parse(strid));
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(3, n, plan.需求发起单位链接.用户数据.单位信息.单位名称, cellXF1); break;
case 2: cell = cells.Add(3, n, plan.物资列表.Count, cellXF1); break;
case 3: cell = cells.Add(3, n, plan.分发列表.Count, cellXF1); break;
case 4: cell = cells.Add(3, n, plan.当前处理单位链接.用户数据.单位信息.单位名称, cellXF1); break;
case 5: cell = cells.Add(3, n, plan.采购方式.ToString(), cellXF1); break;
case 6: cell = cells.Add(3, n, plan.基本数据.添加时间.ToString("yyyy/MM/dd"), cellXF1); break;
case 7: cell = cells.Add(3, n, plan.建议完成时间.ToString("yyyy/MM/dd"), cellXF1); break;
case 8: cell = cells.Add(3, n, plan.联系人, cellXF1); break;
case 9: cell = cells.Add(3, n, plan.联系电话, cellXF1); break;
case 10: cell = cells.Add(3, n, plan.描述, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
Cells cells1 = sheet1.Cells; //获得指定工作页列集合
cells1.Merge(1, 1, 1, 10);
Cell header1 = cells1.Add(1, 1, "需求采购任务分发物资");
header1.Font.Height = 250;
header1.Font.Weight = FontWeight.Bold;
header1.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells1.Add(2, i, "物资名称", cellXF); sheet1.AddColumnInfo(cinfo3); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells1.Add(2, i, "规格型号", cellXF); break;
case 3: cell = cells1.Add(2, i, "计量单位", cellXF); break;
case 4: cell = cells1.Add(2, i, "数量", cellXF); break;
case 5: cell = cells1.Add(2, i, "单价", cellXF); break;
case 6: cell = cells1.Add(2, i, "预算金额", cellXF); break;
case 7: cell = cells1.Add(2, i, "质量技术标准", cellXF); sheet1.AddColumnInfo(cinfo4); break;
case 8: cell = cells1.Add(2, i, "交货期限", cellXF); break;
case 9: cell = cells1.Add(2, i, "采购方式建议", cellXF); sheet1.AddColumnInfo(cinfo5); break;
case 10: cell = cells1.Add(2, i, "备注", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
for (int i = 0; i < plan.物资列表.Count; i++)
{
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.物资名称, cellXF1); break;
case 2: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.规格型号, cellXF1); break;
case 3: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.计量单位, cellXF1); break;
case 4: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.数量, cellXF1); break;
case 5: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.单价, cellXF1); break;
case 6: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.预算金额, cellXF1); break;
case 7: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.技术指标, cellXF1); break;
case 8: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.交货期限.ToString("yyyy/MM/dd"), cellXF1); break;
case 9: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.建议采购方式, cellXF1); break;
case 10: cell = cells1.Add(i + 3, n, plan.物资列表[i].需求计划物资数据.备注, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
Cells cells2 = sheet2.Cells; //获得指定工作页列集合
cells2.Merge(1, 1, 1, 9);
Cell header2 = cells2.Add(1, 1, "需求采购任务分发");
header2.Font.Height = 250;
header2.Font.Weight = FontWeight.Bold;
header2.HorizontalAlignment = HorizontalAlignments.Centered;
for (int i = 1; i < 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells2.Add(2, i, "物资名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells2.Add(2, i, "规格型号", cellXF); break;
case 3: cell = cells2.Add(2, i, "计量单位", cellXF); break;
case 4: cell = cells2.Add(2, i, "收货单位名称", cellXF); break;
case 5: cell = cells2.Add(2, i, "分配数量", cellXF); break;
case 6: cell = cells2.Add(2, i, "提货方式", cellXF); break;
case 7: cell = cells2.Add(2, i, "运输方式", cellXF); break;
case 8: cell = cells2.Add(2, i, "到站", cellXF); break;
case 9: cell = cells2.Add(2, i, "备注", cellXF); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
for (int i = 0; i < plan.分发列表.Count; i++)
{
for (int n = 1; n <= 9; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.物资名称, cellXF1); break;
case 2: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.规格型号, cellXF1); break;
case 3: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.计量单位, cellXF1); break;
case 4: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.收货单位名称, cellXF1); break;
case 5: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.分配数量, cellXF1); break;
case 6: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.提货方式.ToString(), cellXF1); break;
case 7: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.运输方式.ToString(), cellXF1); break;
case 8: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.到站, cellXF1); break;
case 9: cell = cells2.Add(i + 3, n, plan.分发列表[i].需求计划分发数据.备注, cellXF1); break;
}
cell.UseBorder = true;
cell.TopLineStyle = 1;
cell.TopLineColor = Colors.Black;
cell.LeftLineStyle = 1;
cell.LeftLineColor = Colors.Black;
cell.RightLineStyle = 1;
cell.RightLineColor = Colors.Black;
cell.BottomLineStyle = 1;
cell.BottomLineColor = Colors.Black;
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public static void PutOutExcel(string strid, HttpResponseBase rs)//导出电话号码
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "供应商信息电话号码" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("供应商信息电话号码");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 0;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 2; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "供应商名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, 2, "联系号码", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<供应商> model = new List<供应商>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<供应商>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 2; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].企业基本信息.企业名称, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人手机, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
public void Save()
{
string fileName = "TestSave.xls";
XlsDocument doc = new XlsDocument();
doc.FileName = fileName;
string path = Path.Combine(Environment.CurrentDirectory, fileName);
if (File.Exists(path))
File.Delete(path);
Assert.IsFalse(File.Exists(path));
doc.Save();
Assert.IsTrue(File.Exists(path));
File.Delete(path);
}
public static void PutExcelAll(HttpResponseBase rs)//导出供单位用户所有信息
{
//生成Excel开始
XlsDocument xls = new XlsDocument();
xls.FileName = "单位用户列表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("单位用户");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 9;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.TopLineStyle = 1;
cellXF.TopLineColor = Colors.Black;
cellXF.BottomLineStyle = 1;
cellXF.BottomLineColor = Colors.Black;
cellXF.TopLineStyle = 1;
cellXF.TopLineColor = Colors.Black;
cellXF.LeftLineStyle = 1;
cellXF.LeftLineColor = Colors.Black;
cellXF.RightLineStyle = 1;
cellXF.RightLineColor = Colors.Black;
cellXF.Font.Height = 24 * 16;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.Font.Height = 24 * 14;
cellXF1.UseBorder = true;
cellXF1.BottomLineStyle = 1;
cellXF1.BottomLineColor = Colors.Black;
cellXF1.TopLineStyle = 1;
cellXF1.TopLineColor = Colors.Black;
cellXF1.LeftLineStyle = 1;
cellXF1.LeftLineColor = Colors.Black;
cellXF1.RightLineStyle = 1;
cellXF1.RightLineColor = Colors.Black;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "登陆账号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "单位名称", cellXF); break;
case 3: cell = cells.Add(1, i, "单位代号", cellXF); break;
case 4: cell = cells.Add(1, i, "所属管理单位", cellXF); break;
case 5: cell = cells.Add(1, i, "级别", cellXF); break;
case 6: cell = cells.Add(1, i, "联系人", cellXF); break;
case 7: cell = cells.Add(1, i, "联系人职务", cellXF); break;
case 8: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 9: cell = cells.Add(1, i, "手机", cellXF); break;
case 10: cell = cells.Add(1, i, "联系地址", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
IEnumerable<单位用户> model = 用户管理.查询用户<单位用户>(0,0);
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 10; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model.ElementAt(m).登录信息.登录名, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位代号, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.所属单位, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model.ElementAt(m).单位信息.单位级别.ToString(), cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.联系人, cellXF1); break;
case 7: cell = cells.Add(m + 2, n, model.ElementAt(m).联系人职务, cellXF1); break;
case 8: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.固定电话, cellXF1); break;
case 9: cell = cells.Add(m + 2, n, model.ElementAt(m).联系方式.手机, cellXF1); break;
case 10: cell = cells.Add(m + 2, n, model.ElementAt(m).所属地域.地域, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
static void SaveXLS(List<string> codes)
{
try
{
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
XF titleXF = xlsDoc.NewXF();
titleXF.Font.Bold = true;
titleXF.Font.FontName = "宋体";
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add("sheet1");
for (int i = 0; i < codes.Count; i++)
{
sheet.Cells.Add(i + 1, 1, codes[i]);
}
xlsDoc.Save();
}
catch (Exception ex)
{
throw new Exception("生成Excel文件失败", ex);
}
}
public void WriteNorthwindDataSetToXls()
{
string northwindXls = "Northwind.xls";
using (DataSet dataSet = GetNorthwindDataSet())
{
XlsDocument xls = new XlsDocument(dataSet);
SetTestMetadata(xls); //so metadata doesn't differ on different test machines and kill the compare
Assert.AreEqual(NORTHWIND_TABLE_NAMES.Length, xls.Workbook.Worksheets.Count, "Worksheet count");
xls.Save(true);
AssertFilesBinaryEqual(Path.Combine(TestsConfig.ReferenceFileFolder, northwindXls), northwindXls);
}
}
public void SaveNoOverwrite()
{
string fileName = "TestSave.xls";
XlsDocument doc = new XlsDocument();
doc.FileName = fileName;
string path = Path.Combine(Environment.CurrentDirectory, fileName);
File.WriteAllText(path, "test");
Assert.IsTrue(File.Exists(path));
try
{
doc.Save();
}
catch(IOException ex)
{
StringAssert.Contains("already exists", ex.Message);
throw;
}
}
public static void PutExcel(string type, string strid, HttpResponseBase rs)//导出供应商所有信息
{
string idstr = strid;
//生成Excel开始
XlsDocument xls = new XlsDocument();
if (type == "supplier")
{
xls.FileName = "供应商信息表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("供应商");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 7;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 8; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "供应商名称", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "认证级别", cellXF); break;
case 3: cell = cells.Add(1, i, "联系人", cellXF); break;
case 4: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 5: cell = cells.Add(1, i, "主要经营范围", cellXF); break;
case 6: cell = cells.Add(1, i, "是否是全军物资采购供应商", cellXF); break;
case 7: cell = cells.Add(1, i, "供应商类别", cellXF); break;
case 8: cell = cells.Add(1, i, "备注", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<供应商> model = new List<供应商>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<供应商>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 8; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].企业基本信息.企业名称, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].供应商用户信息.认证级别.ToString(), cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人姓名, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model[m].企业联系人信息.联系人手机, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model[m].营业执照信息.经营范围, cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model[m].供应商用户信息.用户来源.ToString(), cellXF1); break;
case 7:
if (model[m].供应商用户信息.协议供应商 && model[m].供应商用户信息.应急供应商)
{
cell = cells.Add(m + 2, n, "应急协议供应商", cellXF1);
}
else if (model[m].供应商用户信息.应急供应商 && !model[m].供应商用户信息.协议供应商)
{
cell = cells.Add(m + 2, n, "应急供应商", cellXF1);
}
else if (model[m].供应商用户信息.协议供应商 && !model[m].供应商用户信息.应急供应商)
{
cell = cells.Add(m + 2, n, "协议供应商", cellXF1);
}
else
{
cell = cells.Add(m + 2, n, "普通供应商", cellXF1);
}
break;
case 8: cell = cells.Add(m + 2, n, "", cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
}
else
{
xls.FileName = "单位用户列表" + DateTime.Now.Second.ToString();
Worksheet sheet = xls.Workbook.Worksheets.Add("单位用户");
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);
cinfo.Collapsed = true;
//设置列的范围 如 0列-10列
cinfo.ColumnIndexStart = 0;//列开始
cinfo.ColumnIndexEnd = 9;//列结束
//cinfo.Collapsed = true;
cinfo.Width = 100 * 60;//列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
//创建列样式创建标题列时引用
XF cellXF = xls.NewXF();
cellXF.VerticalAlignment = VerticalAlignments.Centered;
cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
cellXF.ShrinkToCell = true;
cellXF.TextWrapRight = true;
cellXF.UseBorder = true;
cellXF.Font.Height = 24 * 12;
cellXF.Font.Bold = true;
cellXF.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式创建内容列时引用
XF cellXF1 = xls.NewXF();
cellXF1.VerticalAlignment = VerticalAlignments.Centered;
cellXF1.HorizontalAlignment = HorizontalAlignments.Left;
cellXF1.ShrinkToCell = true;
cellXF1.TextWrapRight = true;
cellXF1.UseBorder = true;
cellXF1.Pattern = 0;//设定单元格填充风格。如果设定为0，则是纯色填充
cellXF1.PatternBackgroundColor = Colors.Red;//填充的背景底色
cellXF1.PatternColor = Colors.Red;//设定填充线条的颜色
//创建列样式结束
Cells cells = sheet.Cells; //获得指定工作页列集合
for (int i = 1; i <= 10; i++)
{ //列操作基本
Cell cell = null;
switch (i)
{
case 1: cell = cells.Add(1, i, "登陆账号", cellXF); break;//添加标题列返回一个列 参数：行 列 名称 样式对象
case 2: cell = cells.Add(1, i, "单位名称", cellXF); break;
case 3: cell = cells.Add(1, i, "单位代号", cellXF); break;
case 4: cell = cells.Add(1, i, "所属管理单位", cellXF); break;
case 5: cell = cells.Add(1, i, "级别", cellXF); break;
case 6: cell = cells.Add(1, i, "联系人", cellXF); break;
case 7: cell = cells.Add(1, i, "联系人职务", cellXF); break;
case 8: cell = cells.Add(1, i, "联系电话", cellXF); break;
case 9: cell = cells.Add(1, i, "手机", cellXF); break;
case 10: cell = cells.Add(1, i, "联系地址", cellXF); break;
}
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
cell.Font.FontFamily = FontFamilies.Modern;//设置字体 默认为宋体
//创建列结束
}
List<单位用户> model = new List<单位用户>();
if (!string.IsNullOrWhiteSpace(idstr))
{
string[] sid = strid.Split(',');
for (int j = 0; j < sid.Length; j++)
{
if (!string.IsNullOrWhiteSpace(sid[j]))
{
model.Add(用户管理.查找用户<单位用户>(long.Parse(sid[j])));
}
}
}
for (int m = 0; m < model.Count(); m++)
{
for (int n = 1; n <= 8; n++)
{
Cell cell = null;
switch (n)
{
case 1: cell = cells.Add(m + 2, n, model[m].登录信息.登录名, cellXF1); break;
case 2: cell = cells.Add(m + 2, n, model[m].单位信息.单位名称, cellXF1); break;
case 3: cell = cells.Add(m + 2, n, model[m].单位信息.单位代号, cellXF1); break;
case 4: cell = cells.Add(m + 2, n, model[m].单位信息.所属单位, cellXF1); break;
case 5: cell = cells.Add(m + 2, n, model[m].单位信息.单位级别.ToString(), cellXF1); break;
case 6: cell = cells.Add(m + 2, n, model[m].联系方式.联系人, cellXF1); break;
case 7: cell = cells.Add(m + 2, n, model[m].联系人职务, cellXF1); break;
case 8:cell = cells.Add(m + 2, n, model[m].联系方式.固定电话, cellXF1);break;
case 9: cell = cells.Add(m + 2, n, model[m].联系方式.手机, cellXF1); break;
case 10: cell = cells.Add(m + 2, n, model[m].所属地域.地域, cellXF1); break;
}
//设置XY居中
cell.HorizontalAlignment = HorizontalAlignments.Centered;
cell.VerticalAlignment = VerticalAlignments.Centered;
//设置字体
cell.Font.Bold = false;//设置粗体
cell.Font.ColorIndex = 0;//设置颜色码
cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体
//创建列结束
}
}
}
rs.ContentType = "application/vnd.ms-excel";
rs.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", xls.FileName));
xls.Save(rs.OutputStream);
}
private void exportAll(string filename)
{
XlsDocument xls = new XlsDocument(); //Create a new MyXls Document
xls.FileName = filename;
Worksheet sheet = xls.Workbook.Worksheets.Add("EDM");
int n = 1;
sheet.Cells.Add(n, 1, "Name");
sheet.Cells.Add(n, 2, "Address");
sheet.Cells.Add(n, 3, "Time");
sheet.Cells.Add(n, 4, "Status");
n++;
foreach (ListViewItem item in this.grid.Items)
{
sheet.Cells.Add(n, 1, item.SubItems[1].Text);
sheet.Cells.Add(n, 2, item.SubItems[2].Text);
sheet.Cells.Add(n, 3, item.SubItems[3].Text);
sheet.Cells.Add(n, 4, item.SubItems[4].Text);
n++;
}
using (var ms = new MemoryStream())
{
xls.Save(ms);
FileStream fs = new FileStream(filename, FileMode.Create);
fs.Write(ms.ToArray(), 0, (int)ms.Length);
fs.Close();
}
MessageBox.Show("Save report ok.");
}
